Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.
To compel a query in Access desktop databases to request criteria once you run it, generate a parameter query. This enables you to apply the same query repeatedly without the need to always access it in Design View to alter the criteria.
Note: This article doesn’t apply to Access web apps.
Understanding the terminology
Before discussing the step-by-step below, it’s useful to get to grips with these terms:
- Parameter – A parameter is a segment of information you provide to a query the moment when you perform it. Parameters can be used alone or as within a bigger expression to create a criterion in the query. You can insert parameters to any of the following types of queries:
- Criteria – Criteria are the “filters” you append to a query to specify the particular items captured after you perform the query.
For further information about the kinds of queries mentioned above, see Introduction to queries.
Create a parameter query
Making a parameter is like creating a usual criterion to a query:
- Produce a select query, and then enter the query in Design view.
- In the Criteria row of your sought field you prefer to set a parameter to, type your desired text to present in the parameter box, contained in square brackets. For example, [Enter the start date:]
- Run through step 2 again for every desired field you aim to set parameters to.
After you commence the query, the prompt emerges excluding the square brackets.
Enter your target value you’re seeking, and then press OK.
You can employ numerous parameters in a criterion. For example, Between [Enter the start date:] And [Enter the end date:] will create two prompts once you perform the query.
Specify parameter data types
You can configure the parameter to take merely a specific kind of data. It is critically vital to define the data type for numeric, currency, or date/time data, because then, users will come across a clearer error message if they input the incorrect type of data, like typing text once a currency value is anticipated.
Note: If a parameter is formatted 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:
- With the query open in Design View, on the Design tab, in the Show/Hide group, pick Parameters.
- In the Query Parameters box, in the Parameter column, state the prompt for each of desired parameter to establish a data type for. Guarantee that each parameter corresponds to the prompt that you applied in the Criteria row of the query design grid.
- In the Data Type column, pick the data type for each parameter.
Add a parameter to a union query
Given that you’re unable to see a union query in the query design grid, you must take a different approach to doing things:
- Launch the union query in SQL view.
- Insert a WHERE clause that has your desired fields to append parameters to. If a WHERE clause already exists, review to discern whether your preferred fields to adjoin parameters to are already in the clause. If they aren’t, create them.
- State your parameter prompt into the where clause, for example, WHERE [StartDate] = [Enter the start date:]
Note that you are required to insert the same filter to each section of the query. In the picture above, the query contains sections (separated by the UNION keyword), so the parameter has to be included twice. Once you run the query, however, the prompt only displays once (assuming you have spelled the prompt exactly the same in each section).
For in-depth 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 traditional criteria, you can merge parameters with the Like keyword and wildcard characters to associate with a broader range of items. For example, you may want your query to prompt for a country/region of origin, but to link with any value that includes the parameter string. To do this:
- Set a select query, and then enter the query in Design View.
- In the Criteria row of your sought field to append a parameter to, input Like “*”&[, your preferred text to apply as a prompt, and then ]&”*”.
After you conduct the parameter query, the prompt launches in the dialogue box except missing the square brackets, and excluding the Like keyword or wildcard characters:
When you input the parameter, the query collects values that include the parameter string. For example, the parameter string us captures items where the parameter field contains a value of Australia and items where the value is USA.
For more details about wildcards, see Using Wildcard characters as criteria.
Return items that don’t match the parameter
Rather than imposing the query to return items that meet your parameter, you may prefer to have the query harvest items that are different from it. For example, you could seek to prompt for a year and then return items where the year is higher than the one you specified. To do this, enter a comparison operator to the left of the first square bracket that embodies the parameter prompt, for example,>[Enter a year:].
Video: Use parameters in queries
Setting a parameter in a query is as simple as making a query that utilises criteria. You can formulate a query to prompt you for one chunk of information, like a part number, or for multiple aspects of information, like two dates. For every parameter, a parameter query presents an individual dialogue box that prompts you for a value for the relevant parameter in question.
Watch this video to learn more about creating parameters in queries.