databasedev.co.uk - database solutions and downloads for microsoft access

Creating Microsoft Access Parameter Queries:

Most of the queries that you will create in Microsoft Access will have pre entered criteria in the query design grid to select a group of records. This article will show you how to enable the database user to input criteria to be used by the query.

In many cases, it would be useful if the user could select a group of records when required (on the fly), by supplying a criteria value for a field when running the query. If you regularly need to retrieve all records of Customers based in a particular location, you do not want to write different queries for each Town or County. It is far better to create a query that will ask the user for the Town or County that they would like to see the results from. Designing the system this way will save you development time, and allow you to create a leaner database.

Parameter Queries

A parameter query is a flexible query that prompts the database user for additional criteria. A message or command is placed in the criteria row beneath the applicable field, with opening and closing brackets [ ] around it. When the query is ran, this text appears as a prompt to the user, asking for input. A parameter query can prompt for multiple criteria, with a separate input box displayed for each criterion.

Single Parameter Query:

An example of the design for a single parameter query, that generates a prompt for the user to enter a Customer Town criteria.

Using the Like Operator and Wildcards in Queries

We can use the Like operator with one or more wildcards to display query results from inexact criteria. Using the Like operator, will allow us to search for records with a value in the field that follow the pattern set by the wildcards used. Wildcards characters can be used in the criteria cell for a select query, or they can be used in response to parameter query prompts. We can see examples of common wildcards shown below:

Wildcard Character	Wildcard Description	Example of Use
*	Matches any number of characters but can only appear at the beginning or end of the string	Like "S*" would match Smith, Stone and Stanley
?	Matches any single alphabetic character and can appear in the middle of a string	Like "B?nd" would match Band, Bend and Bond
[ ]	Matches any single character provided in the brackets	Like "B[ai]nd" would match Band and Bind but not Bond
[! ]	Matches any character not provided in brackets	Like "B[!a]nd" would match Bend and Bond but not Band
[-]	Matches any single character from within the range provided in the brackets	Like "B[a-d]nd" would match Band, Bbnd, Bcnd and Bdnd only
#	Matches any single numeric character and can appear in the middle of a number	Like "9#7" would match 907, 917, 927 and so on

Using Ampersands and Asterisks

You can use the ampersand (&) character along with the asterisk wildcard to link more data to criteria specified by the user as part of a parameter query. For example, you can prompt the user for the first letter of a Customer Name, using the following criteria: Like [Enter the first letter of a Customer Name] & *

Between... And Operator

Another valid operator in a parameter query in the Between.... And operator. To ask for a range of values in a single cell, use the Between... And operator with the bracketed prompts. An example would be something like: Between [StartDate] And [EndDate]

How To Create Parameter Queries

1. View the query in design view
2. In the criteria cell for the appropriate field(s), type in the desired expression within the square brackets([ ])
   
   For example, using a parameter to query for date values:
   
   
   
   When the query is run, Microsoft Access displays this text to prompt the user for the criteria. The text of the prompt must be different from the field name, although it can include the field name.
3. Run the parameter query
4. When you are prompted to enter a parameter value, enter the value of the data that you want to view and click OK.

The Prompt Message

When you specify a prompt message, it should be brief but meaningful. Microsoft Access can display up to 50 or so characters in the prompt message. Do not type periods, commas, exclamation points or square brackets within the outer square brackets.

When we run the above query, we will see a prompt similar to below: