Weekly Computer Tip # 121
30 July 2005

Change search criteria on the fly using parameter queries (Microsoft Access)

This week I did a one-to-one Microsoft Access training session in "one of the UK's leading manufacturers of single and two colour books". At some point we talked about queries to extract a subset of records containing a specific entry in one or more fields. (It's easier said than done!) One of the benefits of a query (as compared to using filters as described in an earlier tip in this series; see http://www.roem.co.uk/tip_107.html) is that a query can be reused over and over again. But there is more ... Instead of you having to anticipate the various combinations of criteria that you are going to need (for example retrieving all clients in a particular city or records that fall between two dates), you can create a so-called parameter query, which will prompt you for information when the query is run. That way you can make changes to the criteria without having to design a completely new query from scratch. (Something this week's course participant, and many before her, really liked!)

Here's how:

1. Create a query as normal.
2. With the Query window displayed in Design view, click in the Criteria row for each field you want to use as a parameter.
3. Type the desired prompt(s) enclosed in square brackets.
4. View the results by clicking on the View or Run button on the toolbar.

Microsoft Access will prompt you to enter the requested criteria. The query will then display those records in which the relevant field contained an entry within the specified date range.

The following are examples of parameter query criteria:

[Enter the city for a list of clients in that town] OR Between [Enter first date:] And [Enter last date:]

Finally, parameter queries are extremely useful when used as the basis for reports. For example, you can create a monthly earnings report based on a parameter query. When you open the report, Microsoft Access will display a dialog box asking for the month that you want the report to cover.

Well, that's it for now. Off to the Cambridge Folk Festival!

Until next week.

PS I’m committed to sharing the best of what I know with others so please don’t keep me a secret. If you enjoyed today’s tip, please forward it to anyone you feel may benefit. Alternatively, feel free to reprint it (with full copyright and subscription information) in your newsletters and message boards.

Home   Training   Support   Clients   Contact Details   News   Disclaimer