Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. Create any type of query in query design view.

2. 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.

3. 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

4. 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 queryfor example, a
reportAccess 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.