Query Criteria, Part 3: Prompting for input by using a parameter

In part 2 of this series, we considered using LIKE and wildcards in query criteria to find inexact matches. Good stuff – but what if you could get the query to apply criteria that are supplied when it is run? What if you could make a query ask for input? Good news! You can make a query ask for input, and it's actually very easy.

All you do is use a question enclosed in square brackets in place of a normal criterion. When someone runs the query, the first thing it does is present a dialog box displaying the question you used. Let's have a look at the design of the query that produced that Customer's last name? dialog box.

The query returns all the data from the Customers table, but only those records where LastName matches the user's answer to this:

That gets the job done, but there are things you can do to improve the query's usability. For one, you can declare your parameters so Access will provide a sensible error message if someone inputs the wrong type of data (for example, someone enters text when a number is required).

To declare a parameter:

1. Open the query in Design view and copy the parameter question to the Clipboard, including the brackets (select the parameter question and press Ctrl+C).

2. On the Query Tools | Design tab, in the Show/Hide group, click Parameters.

3. In the Query Parameters dialog box, paste the copied parameter into the Parameter column, and then set the data type using the drop-down list in the Data Type column.

If you really want to go all-out, you can use a form to collect the input. A form opens up all kinds of possibilities, such as sharing input parameters among a set of queries or providing a date-picker to input a date/time parameter. For more information, see Use parameters in queries, forms, and reports.