Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Building Parameter Queries When You Don't Know the Criteria at Design Time

You, or your application's users, might not always know the parameters for query output when designing the query. Parameter queries let you specify different criteria at runtime so that you don't have to modify the query each time you want to change the criteria.

For example, say you have a query, like the one shown in Figure 4.35, for which you want users to specify the date range of the data they want to view each time they run the query. The following clause has been entered as the criteria for the OrderDate field:

Between [Enter Starting Date] And [Enter Ending Date]

This criterion causes two dialog boxes to appear when the user runs the query. The first one, shown in Figure 4.36, prompts the user with the criteria text in the first set of brackets (refer to Figure 4.35). The text the user types is substituted for the bracketed text. A second dialog box appears, prompting the user for whatever is in the second set of brackets. The user's response is used as the criterion for that query.

Add a parameter to the query qryCustomerOrderSummary so that you can view only TotalPrice summaries within a specific range. Go to the criteria for TotalPrice and type Between [Please Enter Starting Value] and [Please Enter Ending Value] .

This allows you to view all the records in which the total price is within a specific range. The bracketed text is replaced by actual values when the user runs the query. Click OK and run the query. You're then prompted to enter both a starting and an ending value.

To make sure Access understands what type of data should be placed in these parameters, you must define the parameters. Do this by selecting Parameters from the Query menu to open the Query Parameters window. Another way to display the Query Parameters window is to right-click a gray area in the top half of the query design grid; then select Parameters from the context-sensitive, pop-up menu.

The text that appears within the brackets for each parameter must be entered in the Parameter field of the Query Parameters dialog. The type of data in the brackets must be defined in the Data Type column. Figure 4.37 shows an example of a completed Query Parameters dialog box.

You can easily create parameters for as many fields as you want, and you add parameters just as you would add more criteria. For example, the query shown in Figure 4.38 has parameters for the Title, HireDate, and City fields in the Employees table from the Northwind database. Notice that all the criteria are on one line of the query design grid, which means that all the parameters entered must be satisfied for the records to appear in the output. The criterion for the title is [Please Enter a Title]. This means that the records in the result must match the title entered when the query is run. The criterion for the HireDate field is >=[Please Enter Starting Hire Date]. Only records with a hire date on or after the hire date entered when the query is run will appear in the output. Finally, the criterion for the City field is [Please Enter a City]. This means that only records with the city entered when the query is run will appear in the output.

The criteria for a query can also be the result of a function; this technique is covered in Chapter 11.

Chapter 10, "Advanced Report Techniques."