How to Create a Parameter Query in Access

A parameter query is one where, the user supplies criteria with which to filter the results.

When you run a normal query, it will return its results as soon as you run it.

When you run a parameter query, it asks you to enter a value. Once you provide the value, it will continue running, using the value as part of the filtering criteria.

The parameter is provided by the user at runtime. So a parameter query can return different results based on the user's input.

Dates are common parameters in parameter queries. This allows the user to provide a date with which to search. Numbers are also common parameters.

Parameter Query vs a Normal Query

There's almost no difference between creating parameter queries and normal queries. The only difference is that you use square brackets [] to display text to the user.

For example:

Will result in this prompt when the user runs the query:

In this example, the date provided will be used to filter the results in the query to only those albums released after the date provided (because the query has a greater than sign > before the prompt).

Here's a step-by-step guide to creating a parameter query.

1. Start a Normal Query

   First of all, create a query as you would create any other query.

   Click Query Design from the Create tab in the Ribbon.

2. Select the Tables

   Select the tables you'd like to include in your query and click Add to add them to the query. Click Close once you've added the tables.

   In this example, we select the Country and City tables.

3. Add the Fields

   Add the fields you'd like to include in the query by double-clicking them in the top pane.

   The field will appear in the query pane below it.

   In this example, we select the City.CityName, City.Population, and Country.CountryName fields.

4. Apply the Parameter Criteria

   This is where you get to specify the parameter that the user will supply.

   Anything you type between square brackets [] will be displayed as a user prompt. The value that the user supplies will be used as the criteria against the specified field.

   In this example, we use > [Population greater than how many?] as our criteria.

   This query will display cities with a population greater than a given amount (as supplied by the user).

5. Run the Query

   Click the !Run icon on the Ribbon (or switch to Datasheet View) to run the query.

6. Supply the Parameter

   Enter a value at the prompt.

   In this example, we enter 6000000 to indicate that we only want to see cities with a population greater than 6 million.

7. The Results

   The results are returned, based on the parameter supplied.

   Try running the query again, changing the parameter to see how it affects the results.