You can create a query that asks for your input and uses that input as part of the criteria. The input is called a parameter, which just means something that can have different values which determine the behavior or value of something else. For example, temperature, pressure, humidity, and density are parameters that affect the weather. If you vary any of them, you change the weather. In the case of queries, changing a parameter will change the results of the query.
Any query that uses criteria can easily be turned into a parameter query.
Click OK.
You will get a separate dialog for each parameter in the query.
Then the query displays a datasheet of results, using what you typed as criteria.
from LanguageArtsClasses.accdb
The query QCount of Test
Grades by Class, groups the scores for an assignment or test based on the class and letter
grade. The results tell the teacher how many grades of A, B, C, D, and F there are for a particular test or assignment.
The assignment/test ID must be manually entered in the Criteria
line.
The query QCount of Test
Grades by Class-Parameter is more flexible. It has a parameter entered in the Criteria row between square brackets:
[Enter Assignment/Test ID#:]
When the query runs, a dialog appears that
repeats what was typed between the square brackets. When you fill in the value and click OK, the query runs, with
the input as the criteria. No more editing of the query every time you use it! A much more flexible query than before.
An even neater solution involves creating a form with a Lookup field that will show what the choices are so that your user does not have to think too hard. Keep the brain strain to a minimum!
When Access cannot find a value that the query uses, it pops up a
parameter dialog for you to fill in the value.
The most common cause of this confusion is misspelling a field name in an expression in the Query Design View. It can also occur if the query is trying to match records between tables or queries and a record is blank in the field that joins the tables/queries.
The example dialog shows that the Name field does not have a value. In this case there was no such field. It should have been FullName.
![]() |
Step-by-Step: Parameter Query |
![]() |
What you will learn: | to create a parameter query and include choices in the parameter text to create a parameter query with BETWEEN and two parameters for one field to set data type for a parameter |
Start with: , Projects database from previous lesson open.
It is easy to turn a query into a parameter query. Just add a phrase/sentence inside square brackets to the criteria row.
Back up this database now so you will have a good copy to use if you need to start over. This works well while the database is small.
Access 2007:
> Manage > Back Up Database
Access 2010: File > Save and Publish > Back Up Database > Save As button
Access 2013, 2016: File > Save As > Back Up Database > Save As button
In the Projects database, open in Query Design View the
query QStaff-FullName.
This is the query
that calculates a full name based on the first name, middle name, and
last name.
You will modify this query to a parameter query that will show staff members for a particular country.
Save Object As QStaff-WhatCountry.
In the Criteria row for the column Country/Region, type
[What Country/Region?].
Run the query.
A dialog box appears.
Type Argentina in the
dialog box and click on OK.
The datasheet lists only staff members in Argentina.
Return to Query Design View
Run the
query again.
Type USA in the dialog box
and click OK.
The datasheet lists only staff members in the USA.
Return to Query Design View.
Run the
query again.
Type United States in the dialog box and click OK.
The datasheet is blank! None of the records used "United States" in this
field. Access does not know that USA and United States are the same. The user needs some help with what country names will work.
Return to Query Design View.
Edit the criteria to read:
[Which Country/Region: Argentina, Australia, USA?]
Run the query.
The dialog now lists the possible choices. This works well for short lists.
Type australia into the
text box and click OK.
There is only one staff member in the datasheet.
You did not have to capitalize correctly. Sweet!
Save and close the
query.
[QStaff-WhatCountry]
You will create a new parameter query to select projects that have budgets between two amounts. The Criteria row will have an expression with two parameters so that you can enter a minimum and a maximum for the budgets.
In the Criteria row for the Budget column, type
Between [Minimum Budget] And [Maximum Budget]
This expression uses two parameters. The query will show records with a Budget value between the two values, and includes the end values. Access expects you to enter numbers.
Run the query.
The first parameter dialog appears.
Type 2000 and click on OK.
The query runs and shows a datasheet with
5 records. The Projects table currently has 7 records.
Run the query again but in the parameter dialogs, type in the words ten and
one thousand.
An error message appears, but it is not very helpful. What you typed was not too complex and it was not spelled incorrectly (or so I assume!). The problem is that the query needed numbers instead of text.
Error messages from Access are not always very helpful. By giving Access a bit more information, you can get better error messages. The Query Parameters dialog lets you tell Access what data type to expect. Of course, you can tell the user directly what kind of data to enter by creating a longer, more detailed parameter, too.
Run the query again and repeat your text entries as before: ten and one thousand.
You get an error message with the first parameter! It does not tell you what would be valid but at least you know the data type is wrong.
Save the query with the name QProjects-BudgetsBetween and close the
query.