Let's say that you have data in the following table:

This is a list of customer first name, last name, city and state. Let's say that we're interested in getting list of all of the customers in New Hampshire. To extract that information, we'd create a query as follows, specifying the abbreviation for New Hampshire (NH) as criteria in the State field:

Running this query would return two records for the two customers in New Hampshire.

Now let's say that you would also like to create a separate data set for customers in Massachusetts, Texas and Arizona. Rather than create a separate query for each state, we can create one query that will return these results as needed, based on criteria input by the user.

We'd set this up as follows:

Notice that in the criteria row under state we've put "[Enter state]".

When you run this query you get the following prompt (notice that the text above the input box matches the text typed between the brackets in the query form):

If you enter MA you get the following:

As you can see, the resulting set shows the six customers who live in Massachusetts.

The notation for a Parameter Query is simply text within brackets in the criteria field for the variable you’d like to input in the prompt box. The text you enter between the brackets shows up in the box, above the input field. In this Parameter Query, Access takes the criteria [Enter state], replaces it with your input ("MA"), and executes the query.

This is a very powerful feature that can turn a single dedicated query into a much more flexible query that can run “on-the-fly” with dynamic criteria.

Parameter Queries become even more flexible when you use wildcards. Let’s imagine that you wanted to create a data set of every customer who lived in a state starting with the letter N. You could enter the criteria Like [Enter state's First Letter] & "*" under the state column. When the query runs, you'll get the prompt Enter state’s First Letter. In this instance the asterisk will act as a wildcard and you'll get every state that begins with whatever letter you type in. Considering the table in the above example, typing “N” would create a data set of customers in NY and NH. Note that wildcards are very powerful in and of themselves and we'll be reviewing those in a future issue.

You may utilize Parameter Queries for virtually any type of query based on criteria. For example, if you’re interested in counting the number of states in which you have customers, you could use a Group By query (See "Group By" in Help for details on this) to get a count of states “on the fly”. You can also put Parameter Queries into delete and update queries.

It's also possible to designate more than one field for parameters. Let's say there were many more records and you were interested in getting back not only the customers in a particular state, but also customers with a last name that starts with a certain letter. In this case you would have not only the criteria [Enter state] under the state column in the query design, but also [Enter first letter of Customer Last Name] & "*" in the Last Name column. When you run this type of query you'll get two prompts, one for the State and one for the first letter of the Customer Last Name. I know this particular example is not particularly realistic, but I'm sure you understand how multiple parameters can make a query even more flexible.

Parameter Queries are best employed any time when you want a subset of data from a data set but don't know beforehand what that subset will be. Think of them as a query “on the fly”, offering flexibility without the hassle of creating a brand new query.

The most typical uses are for ad hoc questions, or the as the source for reports.

When you use a parameter query as a source for a report, the query (or SQL statement) that provides the data to the report should include a parameter in the query. Then when the report is run, you'll get the prompt and the report will return the records consistent with the entry you make in the parameter dialog box.

Although less common, parameter queries can also be used as the data source for forms. When using these queries in forms and reports, keep in mind that the query will run before the form or report is open. Once the query runs, the form and report will act as it does with any other query or data set.

If you find that you create a number of queries that do essentially the same thing with changing query criteria, consider using a Parameter Query instead. You'll get a much bigger bang for your buck and you'll have a more flexible tool to work with.

Does your database window ever disappear? Most likely, you’ve just inadvertently moved it off the screen. The easiest way to get it back is to choose Windows | Cascade from the menu at the top of the screen. This will show all the active windows in your application, including the database window. Once you see it, you can maximize it and you'll be all set.