Web.QHMIT.com

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:

Screenshot of a creating a parameter query in Access.

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

Screenshot of the user prompt.

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. Screenshot of a creating a parameter query in Access.

    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. Screenshot of a creating a parameter query in Access.

    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. Screenshot of a creating a parameter query in Access.

    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. Screenshot of a creating a parameter query in Access. Screenshot of a close up of the query criteria.

    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. Screenshot of a creating a parameter query in Access.

    Run the Query

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

  6. Screenshot of a creating a parameter query in Access.

    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. Screenshot of a creating a parameter query in Access.

    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.