If you've created regular queries in Access, for example to list staff records from a particular table in surname order, then you may have also used criteria to select only certain records. The Parameter query takes this one step further and allows the user to enter different criteria data each time the query is run, without having to re-edit the query. This is the power of the parameter query.

Parameter queries are created in the query design builder in Access in the same way as you'd build a regular query. Suppose your Access database has a table listing staff records with lots of headings such as first name, surname, date of birth, post, salary and city where each person is based. You first create and save a query based on this table to show a list of all staff showing these three fields first name, surname and city, and you set the sort to be based on surname in ascending order.

So when the query is run all staff are listed in surname order. Next you want to amend the query to show only staff based in a particular city. So back in the query design view you add the required city name in the "criteria" cell under the City field. Then when you run the query you'll see all staff records for that particular city only.

If we change this query to a Parameter Query we can have Access prompt us for the required city name, so we can run the query several times and use a different city as criteria each time the query runs. To do this we edit the query in design view and replace the original city criteria by square brackets. So we could enter [Type a city] as the criteria.

The text entered between the square brackets is the prompt the user will see when the query runs. It's best to first save the query and then test it. When the query is run you should see a prompt "Type a city". If you then type in one of city names used in the table and then click OK the query will list all the staff for that particular city only. Run the query again and type in a different city name at the prompt, and the query lists the staff for this other city.

Suppose the city names are quite long. You can amend the Parameter Query to allow you to type one or more letters of the start of the city name only and then the query will list all staff for the city or cities beginning with what you typed in. To do this we need to edit the query and add a wildcard to the criteria using the * symbol.

A query criteria with a wildcard such as L* (still under the city field in the query design builder) will list records for all cities beginning with "L" (the case does not matter). Just for now delete your original parameter criteria in the query design view and replace it with the criteria L* and then click into the next row down. You'll see that Access has amended your criteria to Like "L*". We'll use this in our amended Parameter Query which results in a much more powerful version.

So staying with our query in design view, remove the Like "L*" criteria completely and replace it with this - Like [Type start of city] & "*" - without the dashes, and then click in the next row down, and then save the query. Now run the query and the prompt should appear with the text "Type start of city". Just type in the first letter of one of the cities, click OK and your query should list all staff in the city or cities starting with what you typed. The query will also work for more than one character typed at the prompt, as long as the characters are at the start of the city name.

This article has looked at the very useful Parameter Query which lets an Access database user enter different criteria into a query each time it is run without the need to re-edit the query, making the query more versatile. The Parameter Query can also be amended to allow the user to only enter partial data, for example the start of a city name, and the query can use this data along with a wildcard to list all records matching cities which start with the chosen criteria.

Interested in learning more about the power of Access queries? A very good way to do this might be to attend one of the many training courses available. The best ones are hands on with lots of practice examples.