Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 1.1 Specify Query Criteria at Runtime

1.1.1 Problem

When you design a query, you don't always know which subset of records you would like to see when you run the query. Instead of creating several queries with the same basic design but slightly different criteria, you'd like to be able to create one query that can be used to return the same fields, but a different set of records, each time it's run.

1.1.2 Solution

Use a parameter query with one or more replaceable parameters that it will request at runtime (when you run the query). This solution demonstrates how you can create and run parameter queries using the default parameter prompt.

Here are the steps to create a parameter query using default prompts:

Create any type of query in query design view.
Choose a field for which you wish to define a parameter. Create a parameter for that field by entering the prompt you would like to see when the query is executed surrounded by square brackets ([]) in the Criteria row for that field. For the example query qryAlbumsPrm1, you would create a parameter for the MusicType field by typing:
```
[Type of Music?]
```
in the Criteria row under MusicType.
Select Parameters from the Query menu to open the Query Parameters dialog, where you declare the parameter. For this example, enter:
```
Type of Music?
```
in the Parameter column of the Query Parameters dialog, and choose:
```
Text
```
from the data type combo box to tell Access that this is a text parameter. This step is optional in this query, but some queries require it (see Recipe 1.1.3), so make it a habit. Steps 2 and 3 are shown in Figure 1-1.

Figure 1-1. The qryAlbumsPrm1 parameter in design view

Save the query and run it. Access will prompt you to enter the type of music with a parameter dialog (see Figure 1-2).

Figure 1-2. The Enter Parameter Value dialog for qryAlbumsPrm1

To see how this works using the sample database, open 01-01.MDB and run the qryAlbumsPrm1 query. You will be prompted for the type of music. Enter a music type, such as rock, alternative rock, or jazz. The query will then execute, returning only the records of the specified music type. For example, if you enter "Alternative Rock" at the prompt, you'll see the datasheet shown in Figure 1-3.

Figure 1-3. The datasheet for qryAlbumsPrm1

1.1.3 Discussion

For queries with simple text parameters, you can get away without declaring the parameter using the Query Parameters command. If you create parameters for crosstab or action queries, however, you must declare the parameter. We recommend that you get in the habit of always declaring all parameters to eliminate any chance of ambiguity. The entries you make in the Parameters dialog end up in the Parameters clause that is added to the beginning of the query's SQL, which you can see by selecting View SQL View.

The result of a parameter query needn't be a query's datasheet. You can base reports, forms, and even other queries on a parameter query. When you run the object that is based on the parameter query—for example, a report—Access knows enough to resolve the parameters prior to running the report.

You can use parameters in any type of query, including select, totals, crosstab, action, and union queries.

[ Team LiB ]