In her weekly series for Cambridge Network members, software training expert Karen Roem offers handy tips to help you 'Tame your computer'.
Tame your computer: parameter queries
This week she explains how to change search criteria on the fly using parameter queries (Microsoft Access) ...
A few months ago I explained how to find and organise your data in Access using .
Similar to filters, a query can be used to extract a subset of records containing a specific entry in one or more fields. One of the added benefits of a query is that it 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.
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:
OR Between And
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.
12 August 2005
Karen Roem offers software training and support through her company Roem Ltd. Contact her by email Karen@roem.co.uk or visit her website at www.roem.co.uk.