Parameter Queries in Access: Ask Users for Input at Runtime

A parameter query is one of the most practical tools in Access. Instead of hard-coding a value in your query criteria — like a specific date or customer name — a parameter query pops up a dialog box and asks the user for the value when the query runs. The same query works for any value the user enters.

What Is a Parameter Query?

A parameter query uses a placeholder in the criteria row instead of a fixed value. When Access runs the query, it sees the placeholder, displays a prompt dialog, and substitutes whatever the user types into the criteria.

The placeholder syntax is square brackets containing a descriptive prompt:

[Enter customer name:]

That text appears in the dialog box, so make it clear and helpful.

Creating a Parameter Query

1. Open the Query Designer (Create → Query Design)
2. Add your table(s)
3. Add the fields you want to display
4. In the Criteria row of the field you want to filter, type your parameter in square brackets:

[Enter start date:]

5. Run the query — Access displays the prompt dialog, you type a value, and the query filters accordingly

Multiple Parameters

You can have as many parameters as you need. Each unique bracketed expression becomes a separate prompt:

SELECT * FROM Orders
WHERE OrderDate >= [Enter start date:]
AND OrderDate <= [Enter end date:]
AND CustomerID = [Enter customer ID:]

Access prompts for each parameter in the order they appear in the query.

Date Range Parameters

Date range parameters are extremely common. The standard pattern:

Criteria for OrderDate: Between [Start date:] And [End date:]

Or in SQL:

WHERE OrderDate Between [Enter start date:] And [Enter end date:]

Users enter dates in their locale's standard format (e.g., 9/16/2026 for US). Access handles the conversion automatically.

Partial Text Matching with Parameters

To let users search for partial text (like typing "Smi" to find "Smith"), combine the parameter with the Like operator and wildcards:

Like "*" & [Enter part of customer name:] & "*"

This matches any record where the field contains the entered text anywhere in the string.

Declaring Parameter Data Types

By default, Access treats parameter values as text. For numeric or date parameters, declare the data type explicitly to avoid type mismatch errors:

1. In the Query Designer, go to Query → Parameters (or the Parameters button on the Design tab)
2. In the Parameters dialog, enter each parameter name (exactly as it appears in brackets) and select its data type
3. Click OK

This ensures Access validates the user's input against the expected type before running the query.

Using Parameters in Reports

Parameter queries work seamlessly as record sources for reports. When you open a report based on a parameter query, Access prompts for the parameters first, then generates the report with the filtered data.

This is a powerful pattern for on-demand reports: one report definition, infinite variations based on user input.

Calling Parameter Queries from VBA

When you run a parameter query from VBA using DoCmd.OpenQuery, Access still shows the parameter prompts. To supply parameters programmatically (without prompts), use a QueryDef:

Dim qdf As QueryDef
Dim rs As Recordset

Set qdf = CurrentDb.QueryDefs("qryOrdersByDate")
qdf.Parameters("[Enter start date:]") = #1/1/2026#
qdf.Parameters("[Enter end date:]") = #12/31/2026#

Set rs = qdf.OpenRecordset()
' Process the recordset...
rs.Close

This lets you build forms with date pickers and pass the selected values to the query without any user prompts.

Limitations

Parameter queries have a few limitations worth knowing:

• No default values — the dialog always starts blank; you cannot pre-fill a default
• No validation — if the user enters the wrong type (text in a date field), they get a generic error
• No cancel — if the user clicks Cancel, the query runs with a null parameter, which may return no records or all records depending on your criteria

For more control over the user experience, build a form with proper controls and pass values to the query via VBA — but for quick, ad-hoc filtering, parameter queries are hard to beat.

Conclusion

Parameter queries are one of the simplest ways to make your Access queries interactive and reusable. A query that prompts for a date range or customer name is infinitely more useful than one with hard-coded criteria. Add them to any query where the filter value changes regularly, and your users will thank you.