Creating Access Parameter Queries

In the last lesson, you saw a query in Design view that had the criterion TX for the State field.
This is fine if you are going to use TX for the criterion forever or do not mind opening the query in Design mode to change the criteria value, but chances are you will want to query on different criteria. Ideally, you could set the field you want to specify criteria for, and be able to specify the criteria values when you run the query. A parameter query will let you do exactly that. Note that you can use parameters for criteria and calculated expressions, but not for specifying whether to sort. Nor can you use them for the name of a field in a query.
For example, to create a parameter query that will allow you to specify different states whenever you run it, first create a query on the Clients table, as you would for any other query. Next, instead of specifying TX for the criteria row of the State field, you can type [Enter State:] in the criteria row of the State field. Now, when you open the query in Datasheet view by clicking the View toolbar button, a dialog box will open asking you to Enter State, as was specified. When you type the state you want, and there are some records that match, Access will display those records.
In addition to standard comparison operators (<, >, =, <=, >=), you can use some advanced operators when defining your parameters.
Besides text, you can also use parameters for numbers and dates. You can use parameters as part of calculated expressions as well.
In the next lesson, you will learn how to use multiple criteria in a query using the AND and OR operators.

It is pretty common to have a report based on a parameter query. For example, you might be interested in viewing customers who have not paid in a specified amount of time, where the amount of time is defined in a parameter value; you might want to view orders for a particular customer, or sales for a given region or country. Fortunately, the Macro Designer allows you to pass parameter values directly in the macro. Start out with a report bound to a parameter query. In this example, I am looking for the name of a particular author that is specified by a value in a combo box named cboAuthors.

OpenReport
Report Name: rptChapters
View: Report
Window Mode: Normal
Parameters
SelectedAuthor: =[cboAuthors]

This macro uses the OpenReport action to open the report in a new window; however, you could just as easily use the BrowseTo action to display the report in a subform control on a form, as shown in the next example. Notice in this case that the BrowseTo action appears twice because a form may or may not be hosted inside a navigation control in Access 2010. An error occurs if the Path to Subform Control argument is invalid, so we’re using the OnError action to allow the case where the action fails.

OnError
Go to: Next
/* Try to load the report in the host form (frmAuthorsParameters) */
BrowseTo
Object Type: Report
Object Name: rptChapters
Path to Subform Control: frmAuthorsParameters.sfrmChild
Data Mode: Edit
Parameters
SelectedAuthor: =[cboAuthor]
/* If this fails, try to load it in the navigation subform */
BrowseTo
Object Type: Report
Object Name: rptChapters
Path to Subform Control: frmMain.sfrmNav>frmAuthorsParameters.sfrmChild
Data Mode: Edit
Parameters
SelectedAuthor: =[cboAuthor]

Click the exercise link below to see how to create a parameter query using the Clients table and the State field.
Creating Parameter - Query