In access, a parameter query allows you to enter criterion when you run the query.

A parameter query in Access lets you change criteria for a query each time the query is run without needing to edit the query. Read on to discover how to do this.

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.

applied tips : march 2007

microsoft access : prompting for queries

Using Access can be daunting to users not familiar with the program. For example, when a database opens, you must select the database object you want to work with from the database window. But what if you don't know, or aren't sure?

If you work with databases often, you probably already know that queries are the true stars of Microsoft Access. Queries make sense out of the thousands of jumbled records and answer the questions you need to know, such as the average price of tea in China, or which customers bought the most parakeet food from your company.

One of the annoying things about queries is that you have to enter a new set of criteria each time you want different information. Modifying query criteria can get old quickly. So what can you do? Create a parameter query.

What is a parameter query?

When you use a parameter query, you're able to quickly enter criteria using prompts that you set up. Prompts could ask you which records you want to retrieve or the value you want to insert in a field. For example, you could insert a Regional Sales parameter that would ask for the name of the state for which you want to retrieve records.

Creating a parameter query is easy. Keep reading to learn more about how to do this.

Creating a parameter query

First, create a new query with the fields you want to use. Or, open the query in which you want to use parameters. You need to specify which field(s) you want to use to group the query, which field(s) you want to be calculated, and which field(s) you want to use to limit the number of records displayed in the query.

In this example, the query is designed to summarize total employee sales. The Cost field is going to be calculated, while the records will be grouped using the FirstName and LastName fields. The Date and State fields will use parameters to limit the number of records displayed in the query. Here's how to insert these parameters.

Inserting parameters

Parameters are inserted in the Criteria row of a field, because you are defining the query's criteria. In essence, a parameter tells Access that some criteria needs to be entered before the query is run. When you enter the parameter, you are entering the message you want to appear when prompted for information.

Make sure the message is clear, so it is easy to recognize what information is being requested. Let's see a parameter in action using our example.

Entering a State field parameter

First, we added a parameter that will limit the records to sales from a specific state. To insert a parameter, all you have to do is click the Criteria row for the field that you want to use as a parameter and type a message, enclosed in [brackets], that you want Access to display when you run the query.

Also, since the State field is only being used as a criteria field, you need to select the "Where" option from its Total row.

Entering a Date field parameter

Let's add one more parameter�one that will use the Date field as criteria to limit the records to those that fall between two dates. Instead of entering a criteria expression with two fixed date values, such as "Between 1/1/00 and 3/31/00," we will create two parameters that will prompt the user to enter the start and end date values each time they run the query.

The Date field has two parameters�the [Enter start date] parameter and the [Enter end date] parameter. Since the Date field is only being used as a criteria field, the "Where" option appears in the Total row.

Running the query

When the parameters are entered, run the query to see how it works by clicking the

Remember, a parameter does not appear when the query is run, it is just used to limit the criteria.

If the parameters are working properly, the prompt for the first parameter should appear.

You could enter any state from the State field, but in this example, the query will find records from Washington.

After entering criteria for the first parameter, either the query is run, or you are prompted for the next parameter.

In this example, there are two more parameters: the start date and the end date. Remember that these parameters were entered separately in the Date field.

Once the last parameter is entered, the results of the query appear.

If the results of the query appear the way you wanted them to, save and close the query so you can use the parameters the next time you run the query.

schedule a class or get consulting on access

Request an on-site Access class from Applied Office or get some helpful consulting. Sessions are just $95/hr.  Learn more here

quick reference card

Get the Quick Reference Card on Microsoft Access! Download it for free and print it on your own printer. You might even want to laminate it.

What is Access parameter criteria?

What is the purpose of a parameter query?

What are variable criteria when running parameter queries?

Where is the parameter query in Access?