Access 2010

How to create a parameter query

A parameter query is one of the simplest and most useful advanced queries you can create. It allows you to create a query that can be updated easily to reflect a new search term. When you open a parameter query, Access will prompt you for a search term and then show you query results that reflect that search.

When you’re running parameter queries, search terms act as variable criteria, which are query criteria that change each time you run the query. For instance, let's say we own a bakery and want to create a query that will quickly look up orders that were placed on a certain date. We could create a parameter query with variable criteria in the Date field. This way, each time we run the query a dialog box will appear prompting us to enter the date we’d like our query to search for.

A prompt in a parameter query

We’ll enter the date we want, then Access will run the query using the date we entered as a search term.

To create and run a parameter query:

1. Create a query as you normally would, modifying the table joins if necessary, selecting the fields to include in your query, and adding any nonvariable criteria to the appropriate fields in the Criteria: row.
2. Locate the field or fields where you would like the variable criteria to appear, and place your cursor in the Criteria: row.
3. Type the phrase you would like to appear in the prompt that will pop up every time you run your query. Make sure to enclose the phrase in brackets [ ]. For example, in our parameter query that searches for orders placed on a certain date, we might type our criteria like this: [What date?].
4. On the Query Design tab, click the Run command to run your query. A dialog box will appear with the prompt you specified. Enter your search term, then click OK to view your query results.
   Running a parameter criteria

To run an existing parameter query, simply open it.

Tips for writing parameter queries

• Ideally, the prompt you create for your query should make it clear what type of information the search term should be, and what format it should be entered in. For example, to guarantee that people enter a search for a date in the format we use in our database, we could write the following in the Criteria: row of the Date field: [What date? (mm/dd/yy)] .
  A more detailed parameter prompt
• The simplest parameter query will give you an exact-match criteria, meaning the query will search for the exact text you enter in the prompt. However, you can turn any type of criteria into a variable criteria. Simply type your prompt text in brackets in the part of the criteria where you would normally put a search term.
  
  For example, in a normal query we could find orders that were placed between two dates by using the criteria Between x AND y, and replacing the x and y with the first and second dates, respectively. To turn this into a parameter criteria, we would simply replace the x and y with the text we want to appear in the prompt. Our variable criteria might look like this: Between [Enter the start date:] AND [Enter the ending date:]. These prompts would appear:
  A parameter query using more complex criteria