Create parameter queries in Access 2016

Parameter queries are one of the simplest and most useful queries you can create. Because parameter queries are so simple, they can be easily updated to reflect the new search terms. When you open a parameter query, Access prompts you to enter a search term and shows you the query results that reflect the search content.

When you run a parameter query, the search term acts as a variable criterion, ie the query criteria changes each time you run the query. For example, suppose you own a bakery and want to create a query to quickly search for orders placed on a certain date. You can create a parameter query with variable criteria in the Date field . This way, every time you run the query, a dialog box will appear prompting you to enter the date you want the search query to be.

Create parameter queries in Access 2016 Picture 1

Just enter the date you want, then MS Access will run the query with the date you entered as that search term.

How to create and run parameter queries

1. Create a query as usual, modify the parameters in the table if necessary, select the fields to include in your query, and add any unmodified criteria to the appropriate fields in the Criteria row : .

2. Locate the field or fields you want the criteria to appear, then select the Criteria: row.

3. Enter the phrase you want to appear in the prompt, which will appear every time you run your query. Be sure to include the phrase in square brackets [] . For example, in the parameter query in this example, the author wants to search for orders placed on a certain date, you can enter the following criteria: [What Date?].

4. On the Design tab , click the Run command to run your query. A dialog box will appear with the specified prompt. Enter a search term and click OK to see your query results.

Create parameter queries in Access 2016 Picture 2

You can simply open an existing parameter query to run it.

Tips for writing parameter queries

Ideally, the prompt you create for your query must clarify the type of search term information, as well as the desired format. For example, to make sure the user enters a search for a date in the format used in your database, you can write the following information in the Criteria: row of the Pickup Date field as follows: [What Date? (mm / dd / yyyy)] .

Create parameter queries in Access 2016 Picture 3

The simplest parameter query will give you the exact match criteria, meaning that the query will search for the exact text you entered in the prompt. However, you can turn any type of criteria into a variable criterion. Just enter the text you need in parentheses in the criteria section, where you usually put the search phrase.

For example, in a normal query, you can find orders placed between two dates using the Between x AND y criteria, then replace x and y corresponding to the start date and end date. To turn this into parameter criteria, you simply need to replace x and y with the text you want to appear in the prompt. Your variable criteria may look like the following: Between [Enter the start date:] And [Enter the end date:] . These two prompts will appear when you run the query.

Create parameter queries in Access 2016 Picture 4

See more:

1. Options to create data queries in Access 2016
2. Create data queries in Access 2016 from simple to complex
3. Introduction to tables, queries, forms, reports in Access