How To Create A Query In Access 2007 With Criteria

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 Run button on the toolbar.

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.