Creating Access Parameter Queries to specify Criteria dynamically

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.

Along with your standard comparison operators (<, >, =, <=, >=), you can also use the keywords Like and Between in your criteria. These operators give you some flexibility and convenience over the standard operators. Take a look.

• Using the Like operator:
  The Like operator allows you to search based on partial fields, unlike the = operator, which allows you to search only based on the entire field.
  For example, in the simulation for this lesson, you have to type the full name of the state to have records returned. This is not bad for using the State field, because it is only two letters. But what about if you wanted to use City?
  The criteria row in the City field would have to be set to =[Enter City]. To take advantage of the Like operator, you would type Like [Enter City] & “*”. Note the use of the asterisk (*) at the end of the criteria. When the query is run again and you enter S, you will see all the cities that start with S, such as Seattle and San Francisco. You can see them here:
  

  

• Using the Between Operator
  The Between operator allows you to look for criteria between (and including) two values. For example, if you want to find the records in the Hours table with DateWorked values between two dates, you would set the criteria row in the DateWorked column to Between [Enter Start] And [Enter End]. If the dates entered were 6/1/2009 and 6/15/2009, you would get those records whose DateWorked values fall between the two dates (including the two dates), as shown below.
  

  

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]

The following section below discusses how to create a parameter query using the Clients table and the State field.

For further practice, try this on your own in Access, using another instance. Although it cannot be graded, you can post what you have learned in the discussion area. Let's review the correct steps to take:

1. Here is a query that displays all the company names and the states they are located in. Add a parameter that will ask the user to enter a specific state, and return the results. In the first criteria row for State, type [Enter State:], then click the View button on the toolbar to execute the query.
2. Here is the parameter box you created. Type in TX, then click the OK button.
3. There you go. The companies that are located in Texas are now displayed.

In the next lesson, you will learn how to use multiple criteria in a query using the AND and OR operators.