Tables & Queries:
Parameter Query

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 the 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.

How To Create a Parameter Query

Any query that uses criteria can easily be turned into a parameter query.

• Decide on the field or fields for which you want to use parameters.

• In Query Design View, in the Criteria row for the field, enter a phrase or sentence between square brackets. Be sure that the phrase makes it clear what kind of value should be entered.

• When the query runs, a dialog appears which shows what you typed between the brackets. Enter an appropriate value.

• Click OK and the query finishes, using what you typed as criteria.

Example: Parameter Query

from LanguageArtsClasses.mdb

The query QCount of Test Grades by Class, groups the scores for an assignment or test based on the class and letter grade. A particular assignment/test ID has to be entered in the Criteria line. It is more flexible to have the query ask which assignment to look at.

By writing [Enter Assignment/Test ID#:] in the Criteria cell instead of entering a specific ID number, 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. A much more flexible query than before!

In this case the user had to know the ID number of the assignment or test that she wants to look at. You can also create a form with a Lookup field that will show you what the choices are. When the choices are not completely obvious or the list is long or there are multiple parameters, a form can make it much easier to enter the right values. Keep the brain strain to a minimum!

Problem: Unexpected parameter dialog.

When Access cannot find a value that the query asks for, it pops up a parameter dialog for you to fill in the value. The dialog shows the field that needs a value and its source, whether a table or query. The problem can be with an expression in the query or with a record. Access does not tell you which!

The most common cause of this confusion is mis-spelling 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 FullName field does not have a value. The query was one that Access created in the background as part of a complex query to calculate grades. A record in the Scores table had an ID number but was blank otherwise. Access tried to match the blank score with a student, using the StudentID, in order to calculate the FullName. Naturally it failed to find a student with a blank StudentID. In this case, clicking OK without typing in anything ran the rest of the query just fine.

Step-by-Step: Parameter Query

---

What you will learn:

to create a parameter query

---

Start with:  , Projects database open.

Create a Parameter Query

It is easy to turn a query into a parameter query. Just add a phrase/sentence inside square brackets to the criteria row.

1. 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.
    

2. Save the query as QStaff-WhatCountry.
    

3. In the Criteria row for the column Country/Region, type [What Country/Region?] and click OK.
    

4. Run the query. A dialog box appears.
    

5. Type Argentina in the text box.
   The datasheet lists only staff members in Argentina.
   

6. Return to Query Design View
    

7. Run the query again.
    

8. Type USA in the text box and click OK.
   The datasheet lists only staff members in the USA.
   

9. Return to Query Design View.
    

10. Run the query again.
     

11. Type United States in the text 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.
     

12. Return to Query Design View.
     

13. Edit the criteria to read:
    [Which Country/Region: Argentina, Australia, USA?]
     

14. Run the query.
    
    The dialog now lists the possible choices. This works well only for short lists.
     

15. Type Australia into the text box and click OK.
    There is only one staff member in the datasheet.
    
     

16. Save the query.  (QStaff-WhatCountry)

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012