A parameterised query in Access

Set-up

1. Download the TXT file
2. Import the TXT data into a new table in a new database (the data is delimited)
3. Use the Query Wizard to create a new query based on the table, including all the fields

Simple parameter

In the simple example that follows, we want to be able to query records based on gender. We want to be able to specify Male or Female at run time.

1. Create a query based on the PopStars table
2. Add the fields you require, including the Gender field
3. In the Criteria row for the Gender field, enter the following text: Like [Female or Male: ]
4. Run the query
5. The Enter Parameter Value prompt will appear
6. Enter Female (or Male)
7. Click the OK button

Here is a REAL gotcha! You cannot use a parameter that is the same as the field name. In other words, in the Gender field, your parameter cannot be [Gender]. It must be [Enter your gender] or even just [Gender:]

Parameter with wildcard

Now replace the criteria in the Gender field in the query above with the following: Like [F(emale) or M(ale): ] & "*"

The & (ampersand) is Access’ concatenator. It “joins” strings together — in this case, it joins the user’s input with the * (wildcard) operator to form the criteria.

You will now only need to enter F (or M) to obtain the same results.

Dynamic parameters can be combined with static parameters.

Next steps

Coming soon: Create a Crosstab Query in Access