Parameter query microsoft access 2003

For a Microsoft Access 97 version of this article, see In Microsoft Access, you can use variable parameters in queries. This article discusses how to construct a query that requires more than one prompt. You can use each parameter both as criteria and as a field to allow complicated evaluation of the value that is entered in each parameter.

You may want to back up the Northwind. The following parameter query is based on the Orders table in the sample database Northwind. It selects orders written between two dates provided by the user. If the user does not enter either the Start Date or the End Date, the query returns all dates greater than or equal to the Start Date, or less than or equal to the End Date.

If the user enters neither a Start Date nor an End Date, the query returns all orders. In Access or in Access , click Datasheet on the View menu to run the query. In Access , click the Design tab, and then click Datasheet View in the View list in the Results group to run the query. Note Access prompts you for the value of the parameters. Then, Access substitutes the proper values in the query. Report abuse. Details required :. Cancel Submit. Daniel Pineault MVP. When you open Access or when you open a specific database?

I'm thinking the latter. How satisfied are you with this reply? For example, you could create a Regional Sales query that would ask for the name of the state that you want to filter by. Creating a parameter query is easy. 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, as shown in figure. As always, you need to add the field that you want to group data by onto the design grid.

This time we want to create a query that summarizes total employee sales. Here are the fields that we will use in the query:.

The Total row appears. 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.

This will total the Cost field. We will use the Date field as criteria to limit the records to those that fall between two dates. You've just created two parametersthe [Enter start date] parameter and the [Enter end date] parameter. Since the Date field is only being used as a criteria field, you need to select the "Where" option from its Total row.

The "Show" check box automatically unchecks itself, indicating that the Date field will not appear in the query results. Let's add one more parameterone that prompts the user to enter the state where the tour was sold.