How to Create a Parameter Query

Parameter Queries are a great way to add interactivity to your Access Database.  Instead of entering a fixed Query Criteria in the Query Design Grid, we enter a question which prompts the user to enter the criteria in a dialogue box when the query is run. In this exercise we are going to create a Parameter Query to produce a list of surnames based on a value entered by the user.

1. Create a table with three columns - ID, Firstname, and Surname.
2. Enter a some test data into the table. Between five and ten random names should do for this purpose.  You might like to give some of the records the same surname.
3. Create a new query by clicking the QUERY DESIGN icon.  This is located in the OTHERS group of the Access CREATE ribbon. 
4. Once the Query Design Window opens, select the Table you just created from the SHOW TABLE dialogue box.
5. Then select the three fields contained in this table. The quickest way of doing this is to double click each field name listed in the table box in the upper section of the screen.  These will then appear as field headings in the QUERY DESIGN GRID.
6. The last stage is to enter the user prompt in the criteria row of the Surname column.  As you may remember, this has to be enclosed within square brackets.  Our prompt is going to be [Enter Surname].

Figure 1: A Parameter Query created in the Query Design Grid.

Then when you run the query you get this message:

Figure 2: Enter Parameter Value Dialogue Box.

All you need to do now is enter one of the surnames you added to your test data in stage 2, and click OK.  Then any record with that surname is produced in your query results.

Another great thing about Parameter Queries that you might like to try, is using them as the record source for a form.  When the form is loaded the query runs asking you to enter a parameter value as before.  This time, however, once you click OK, the form opens displaying a set of records based on the value entered.