Free Access Training - Parameter Values & Reports

Microsoft Office Training > Access Training > Free Computer Training > Including Parameter Values in a Microsoft Access Report

Please feel free to use this content on your web site, but please include the links at the end

Including Parameter Values in a Microsoft Access Report

This exercise is for Access users who have some experience with parameter queries and creating their own reports.  A parameter prompts the end user to enter criteria each time the query is run. This exercise shows you how to display the parameter value that is given by the end user in the heading of a report based on the query. 

There are three main steps we need to complete to demonstrate this:
1) Create a table
2) Create a parameter query based on the table
3) Create a report based on the query

1. Create a new table and save it as tblEmployees.  The table will need to hold the following fields which you should create in design view:

Field Name                 Data Type
EmployeeID                AutoNumber
FirstName                   Text
LastName                    Text
Location                      Text

Set Employee ID as the primary key

Enter the following records into this table

EmployeeID    FirstName       LastName        Location
1                      Andy               Bean                London
2                      Claire               Doubt              London
3                      Enid                Fairview          Brighton
4                      Geoff              Hall                 Brighton
5                      Ivan                 Jones               London

Close the table

2. Create a query based on this table, adding all the fields to the query grid.  Then add the following parameter for the Location field.
   
   [Which location?]
   
   Then in the next available column in the query grid type the following into the Field row
   
   ParaLoc:[Which location?]

ParaLoc is just the name we have given our new column – it’s not a special function or anything like that.  If you are experienced user of parameter queries you may wonder we have created this column.  Well essentially it is so we can store the parameter value in a field and then refer to that field in the report.

Save the query as qryLocation and close it.

3. Create a report based on this query using the Report Wizard.  Use all the fields except maybe the EmployeeID field. Once the report has been created switch to design view and in the report header delete the current report heading including its box.

Open the form toolbox and draw a text box where the report heading used to be.  Delete the label for the text box and then type the following into the text box:

=“Employees Report for&“ ”&[What Location?]

Print preview the report:  the parameter value that you entered for the location should appear in the heading of the report.  For example if you entered London as your location, the heading would read - Employees Report for London

Microsoft Office Training with Blue Pecan Computer Training. Tailored Microsoft Access Training.