Including Parameter Values in a Microsoft Access Report

December 23, 2017 by  Filed under: Computer 
 

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

Normal 0 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable{mso-style-name:”Table Normal”;mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-parent:””;mso-padding-alt:0cm 5.4pt 0cm 5.4pt;mso-para-margin:0cm;mso-para-margin-bottom:.0001pt;mso-pagination:widow-orphan;font-size:10.0pt;font-family:”Times New Roman”;mso-ansi-language:#0400;mso-fareast-language:#0400;mso-bidi-language:#0400;}

 

There are three main steps we need to complete todemonstrate 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 followingfields which you should create in design view:

 

Field Name        DataType

EmployeeID        AutoNumber

FirstName         Text

LastName          Text

Location          Text

 

Set EmployeeID as the primary key

 

Enter thefollowing 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 thetable

     

2. Create a query based on this table, adding all thefields to the query grid.  Then add thefollowing parameter for the Location field.

 

[Which location?]

 

Then in the next available column in the query grid typethe 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 parameterqueries you may wonder we have created this column.  Well essentially it is so we can store theparameter value in a field and then refer to that field in the report.

 

Save the queryas qryLocation and close it.

 

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

 

Open the form toolbox and draw a text box where thereport heading used to be.  Delete thelabel 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 inthe heading of the report.  For exampleif you entered London as your location, the heading would read – EmployeesReport for London

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!


You must be logged in to post a comment.

Prev Post:
Next Post: