Parameter Queries : MS Access

For example, you could create a Regional Sales query that would ask for the name of the state that you want to filter by.

Enter a by entering a message or prompt enclosed in [brackets] in the field's criteria row.

The parameter query prompts the user to enter the state.

The results of the parameter query.

Creating a parameter query is easy. All you have to do is click the Criteria row for the field that you want to use as a parameter and type a message, enclosed in [brackets], that you want Access to display when you run the query, as shown in figure.

Click the qryTourSales query and then click the Design button.

As always, you need to add the field that you want to group data by onto the design grid. This time we want to create a query that summarizes total employee sales. Here are the fields that we will use in the query:
In the tblEmployees field list, double-click the FirstName, LastName, and State fields, and in the tblCustomerTours field list, double-click the Cost and Date fields.

Next you need to tell the query that you want to group and summarize the query.
If necessary, click the Totals button on the toolbar.

The Total row appears. You need to specify which field(s) you want to use to group the query, which field(s) you want to be calculated, and which field(s) you want to use to limit the number of records displayed in the query.

You want to group records using the FirstName, LastName, and State fields and find the total of the Cost field.
Click the Cost column's Total row, click the list arrow, and select Sum from the list.

This will total the Cost field. We will use the Date field as criteria to limit the records to those that fall between two dates. Instead of entering a criteria expression with two fixed date values, such as "Between 1/1/00 and 3/31/00," we will create two parameters that will prompt the user to enter the two date values each time they run the query.
Click the Date column's Criteria row and type Between [Enter start date] and [Enter end date].

You've just created two parametersthe [Enter start date] parameter and the [Enter end date] parameter.

Since the Date field is only being used as a criteria field, you need to select the "Where" option from its Total row.
Click the Date column's Total row, click the list arrow, and select Where from the list.

The "Show" check box automatically unchecks itself, indicating that the Date field will not appear in the query results.

Let's add one more parameterone that prompts the user to enter the state where the tour was sold.
Click the State column's Criteria row and type [Enter the state].

This will prompt the user to enter the name of the state.

Since the State field is only being used as a criteria field, you need to select the "Where" option from its Total row.
Click the State column's Total row, click the list arrow, and select Where from the list.

Let's test our query.
Click the Run button on the toolbar.

Access prompts you to enter the first parameter, as shown in figure. You want to summarize records from Washington.
Type WA and click OK.

Access prompts you for the next parameterthe start date.
Type 1/1/00 and click OK.

Access prompts you for the last parameterthe end date.
Type 6/30/00 and click OK.

Access displays the results of the parameter query, as shown in figure. The order of your query may be different if you put the FirstName field on the design grid before the LastName field.
Save the query as qryParameter and then close the query.

Some advanced Access developers use custom-made forms to provide parameter queries with their information. If developers bind a parameter to the controls on a form (such as [frmCustomers]![Name]), users can fill out one dialog box instead of having to fill out five or six pop-up dialog boxes.

To create a parameter query:

display the query in design view.
click the criteria row for the field you want to use for your parameter criteria and enter the text of the prompt, surrounded by square brackets [ ].
click the view button or run button on the toolbar to run the query.
enter a criteria value in response to the prompt and click ok.