Use parameters to ask for input when running a query

To make a query in Access desktop databases ask for criteria when you run it, create a parameter query. This allows you to use the same query over and over without having to constantly open it in Design view to edit the criteria.

Note: This article doesn't apply to Access web apps.

Understanding the terminology

Before getting into the step-by-step below, it's helpful to understand these terms:

• Parameter   A parameter is a piece of information you supply to a query right as you run it. Parameters can be used by themselves or as part of a larger expression to form a criterion in the query. You can add parameters to any of the following types of queries:

  • Select

  • Crosstab

  • Append

  • Make-table

  • Update

• Criteria   Criteria are the "filters" you add to a query to specify which items are returned when you run the query.

For more information about the types of queries mentioned above, see Introduction to queries.

Create a parameter query

Creating a parameter is similar to adding a normal criterion to a query:

1. Create a select query, and then open the query in Design view.

2. In the Criteria row of the field you want to apply a parameter to, enter the text that you want to display in the parameter box, enclosed in square brackets. For example, [Enter the start date:]

   

3. Repeat step 2 for each field you want to add parameters to.

When you run the query, the prompt appears without the square brackets.

Fill in the value you're looking for, and then click OK.

You can use multiple parameters in a criterion. For example, Between [Enter the start date:] And [Enter the end date:] will generate two prompts when you run the query.

Specify parameter data types

You can set the parameter to accept only a certain type of data. It is especially important to specify the data type for numeric, currency, or date/time data, because then, people will see a more helpful error message if they enter the wrong type of data, such as entering text when a currency value is expected.

Note: If a parameter is configured to accept text data, any input is interpreted as text, and no error message is displayed.

To specify the data type for parameters in a query:

1. With the query open in Design view, on the Design tab, in the Show/Hide group, click Parameters.

2. In the Query Parameters box, in the Parameter column, enter the prompt for each parameter you want to specify a data type for. Make sure that each parameter matches the prompt that you used in the Criteria row of the query design grid.

3. In the Data Type column, select the data type for each parameter.

Add a parameter to a union query

Since you can't view a union query in the query design grid, you'll need to do things a little differently:

1. Open the union query in SQL view.

2. Add a WHERE clause that contains the fields you want to add parameters to.

   If a WHERE clause already exists, check to see whether the fields you want to add parameters to are already in the clause. If they aren't, add them.

3. Type your parameter prompt into the where clause, for example, WHERE [StartDate] = [Enter the start date:]

   

   Note that you need to add the same filter to each section of the query. In the picture above, the query has two sections (separated by the UNION keyword), so the parameter needs to be added twice. When you run the query, however, the prompt only appears once (assuming you have spelled the prompt exactly the same in each section).

For more information about union queries, see Use a union query to view a unified result from multiple queries.

Combine parameters with wildcards for more flexibility

As with normal criteria, you can combine parameters with the Like keyword and wildcard characters to match a wider range of items. For example, you might want your query to prompt for a country/region of origin, but to match any value that contains the parameter string. To do this:

1. Create a select query, and then open the query in Design view.

2. In the Criteria row of the field you want to add a parameter to, type Like "*"&[, the text that you want to use as a prompt, and then ]&"*".
   
   

When you run the parameter query, the prompt appears in the dialog box without the square brackets, and without the Like keyword or wildcard characters:

After you enter the parameter, the query returns values that contain the parameter string. For example, the parameter string us returns items where the parameter field has a value of Australia and items where the value is USA.

For more information about wildcards, see Using Wildcard characters as criteria.

Return items that don't match the parameter

Instead of having the query return items that match your parameter, you might want the query to return items that don't match it. For example, you might want to prompt for a year and then return items where the year is greater than the one you entered. To do this, type a comparison operator to the left of the first square bracket that encloses the parameter prompt, for example,>[Enter a year:].

Video: Use parameters in queries

Using a parameter in a query is as easy as creating a query that uses criteria. You can design a query to prompt you for one piece of information, such as a part number, or for more than one piece of information, such as two dates. For each parameter, a parameter query displays a separate dialog box that prompts you for a value for that parameter.

Watch this video to learn more about creating parameters in queries.

See Also

Use parameters in queries, forms, and reports