How do you enter parameter criteria in Access?

The best part about queries is that you can save and run the same query again and again, but when you run the same query again and again by only changing the criteria then you might consider the query to accept parameters.

Nội dung chính Show

Parameter Query vs a Normal Query
Start a Normal Query
Select the Tables
Add the Fields
Apply the Parameter Criteria
Run the Query
Supply the Parameter
The Results
What does it mean to enter a parameter value in Access?

If you frequently want to run variations of a particular query, consider using a parameter query
Parameter query retrieves information in an interactive manner prompting the end user to supply criteria before the query is run.
You can also specify what type of data a parameter should accept.
You can set the data type for any parameter, but it is especially important to set the data type for numeric, currency, or date/time data.
When you specify the data type that a parameter should accept, users see a more helpful error message if they enter the wrong type of data, such as entering text when currency is expected.
If a parameter is set to accept text data, any input is interpreted as text, and no error message is displayed.

Example

Let us now take a look at a simple example by creating a parameter query. Let us open your database and select Query Design in the Create table tab.

Double-click on the tblProjects and close the Show dialog box.

Select the field you want to see as a query result as shown in the following screenshot.

In the query design grid, in the Criteria row of the ProjectStart column, type [Enter a project start data]. The string [Enter a project start data] is your parameter prompt. The square brackets indicate that you want the query to ask for input, and the text is Enter a project start data is the parameter prompt displays.

Let us now run your query and you will see the following prompt.

Let us now enter the following date.

Click OK to confirm.

As a result, you will see the details of the project which started on 2/1/2007. Let us go to the Design View and run the query again.

Enter the date as in the above screenshot and click Ok. You will now see the details of the project which started on 5/1/2008.

A parameter query is one where, the user supplies criteria with which to filter the results.

When you run a normal query, it will return its results as soon as you run it.

When you run a parameter query, it asks you to enter a value. Once you provide the value, it will continue running, using the value as part of the filtering criteria.

The parameter is provided by the user at runtime. So a parameter query can return different results based on the user's input.

Dates are common parameters in parameter queries. This allows the user to provide a date with which to search. Numbers are also common parameters.

Parameter Query vs a Normal Query

There's almost no difference between creating parameter queries and normal queries. The only difference is that you use square brackets [] to display text to the user.

For example:

Will result in this prompt when the user runs the query:

In this example, the date provided will be used to filter the results in the query to only those albums released after the date provided (because the query has a greater than sign > before the prompt).

Here's a step-by-step guide to creating a parameter query.

Start a Normal Query
First of all, create a query as you would create any other query.
Click Query Design from the Create tab in the Ribbon.
Select the Tables
Select the tables you'd like to include in your query and click Add to add them to the query. Click Close once you've added the tables.
In this example, we select the Country and City tables.
Add the Fields
Add the fields you'd like to include in the query by double-clicking them in the top pane.
The field will appear in the query pane below it.
In this example, we select the City.CityName, City.Population, and Country.CountryName fields.
Apply the Parameter Criteria
This is where you get to specify the parameter that the user will supply.
Anything you type between square brackets [] will be displayed as a user prompt. The value that the user supplies will be used as the criteria against the specified field.
In this example, we use > [Population greater than how many?] as our criteria.
This query will display cities with a population greater than a given amount (as supplied by the user).
Run the Query
Click the !Run icon on the Ribbon (or switch to Datasheet View) to run the query.
Supply the Parameter

Enter a value at the prompt.
In this example, we enter 6000000 to indicate that we only want to see cities with a population greater than 6 million.
The Results
The results are returned, based on the parameter supplied.
Try running the query again, changing the parameter to see how it affects the results.

What does it mean to enter a parameter value in Access?

Sometimes when you open an Access object (such as a table, query, form, or report), Access displays the Enter Parameter Value dialog box. Access displays this dialog box when you open an object that contains an identifier or expression that Access can't interpret. In some cases, this is the behavior that you want.