Including Parameter Values in an Access Report :www.bluepecan.co.uk Including Parameter Values in an Access Report Microsoft Office Training Access Training


The task at hand… :www.bluepecan.co.uk The task at hand… 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.


The steps :www.bluepecan.co.uk The steps 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


The Employees Table :www.bluepecan.co.uk The Employees Table 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


The Employees Table :www.bluepecan.co.uk The Employees Table 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


Create a Parameter Query :www.bluepecan.co.uk Create a Parameter Query 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?]


Create a Parameter Query :www.bluepecan.co.uk Create a Parameter Query 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.


Create the Report :www.bluepecan.co.uk Create the Report 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?]


Create the Report :www.bluepecan.co.uk Create the Report 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


More Access Tutorials :www.bluepecan.co.uk More Access Tutorials For more Access Tutorials visit the Blue Pecan Free Microsoft Office Training page. For a direct link to this Access tutorial