Designing Parameter Queries

If you expect to run a summary or another type of query repeatedly with changes to the criteria, you can convert the query to a parameter query. Parameter queries which Chapter 9 explained briefly enable you to enter criteria with the Enter Parameter Value dialog. Access prompts you for each parameter. For the qryMonthlyOrders1997 query that you created in the preceding section, the only parameter likely to change is the range of dates for which you want to generate the product sales data. The two sections that follow show you how to add a parameter to a query and specify the data type of the parameter.

Adding a Parameter to the Monthly Sales Query

To convert the qryMonthlyOrders1997 summary query to a parameter query, you first create prompts for the Enter Parameter Value dialog that appears when the query runs. You create parameter queries by substituting the text with which to prompt the user, enclosed within square brackets, for actual values. Follow these steps:

1. Open in Design view the qryMonthlyOrders1997 query that you created in the preceding section.

2. With the cursor in the Month column's Field row, press F2 to select the expression in the Field cell. Then press Ctrl+C to copy the expression to the Clipboard.

3. Move the cursor to the OrderDate column's Field row and press F2 to select OrderDate. Then press Ctrl+V to replace OrderDate with the expression used for the first column.

4. Move to the OrderDate column's Criteria cell and replace Like "*/*/1997" with [Enter the year and month in YYYY-MM format:] (see Figure 11.55).

   Figure 11.55. Specify the same format for the parameter column as that of the grouping column, and add the prompt for the Enter Parameter dialog.

   

5. Choose File, Save As, and save the query as qryMonthlyOrdersParam.

6. Click the Run button. The Enter Parameter Value dialog opens with the label that you assigned as the value of the criterion in step 4.

7. Type 1997-06 in the text box to display the data for June 1997, as shown in Figure 11.56.

   Figure 11.56. You must type the parameter exactly as shown in the Enter Parameter Value dialog's prompt to return rows.

   

8. Click OK to run the query. The result appears as shown in Figure 11.57.

   Figure 11.57. This datasheet shows the result of the parameter value entered in step 7.

   

Specifying the Parameter's Data Type

The default field data type for parameters of Access queries is Text. If the parameter creates a criterion for a query column of the Date/Time or Number field data type, you must assign a data type to each entry that is made through an Enter Parameter Value dialog. Data types for values entered as parameters are established in the Query Parameters dialog. If you have more than one parameter, you can establish the same or a different data type for each parameter.

Note

The data type for the prompt of the qryMonthlyOrdersParam query's parameter is Text (the default), not Date/Time. Thus you don't need to apply a data type specification for the query.

Follow these steps to demonstrate adding an optional data type specification to a parameter:

1. Return to Design view, use the mouse to select the prompt text only in the Month column's Criteria cell (omit the square brackets and colon character), and copy the text of the prompt to the Clipboard by pressing Ctrl+C.

2. Choose Query, Parameters to display the Query Parameters dialog.

3. To insert the prompt in the Parameter column of the dialog, place the cursor in the column and press Ctrl+V. The prompt entry in the Parameter column must match the prompt entry in the Criteria field exactly; copying and pasting the prompt text ensures an exact match. Don't include the square brackets in the Parameter column.

4. Press Tab to move to the Data Type column, press Alt+[da] to open the Data Type drop-down list, and select Date/Time (see Figure 11.58). Click Cancel to close the dialog without adding the Date/Time data type, because it isn't applicable to this query.

   Figure 11.58. Select the data type for the parameter's prompt from the Data Type list in the Query Parameters dialog.

   

5. If you applied the Date/Time data type to the qryMonthlyOrdersParam query, reopen the Query Parameters dialog and delete the prompt text, which also deletes the data type entry.

Tip

Complete your query design and testing before you convert any type of query to a parameter query. Using fixed criteria with the query maintains consistency during the testing process. Furthermore, you can make repeated changes between Design and Run view more quickly if you don't have to enter one or more parameters in the process. After you finish testing the query, edit the criteria to add the prompt for the Enter Parameter Value dialog.

The parameter-conversion process described in this section applies to all types of queries that you create if one or more of the query columns includes a criterion expression. The advantage of the parameter query is that you or a user of the database can run a query for any range of values in this case, dates such as the current month to date, a particular fiscal quarter, or an entire fiscal year.