Tables & Queries:
Parameter Query

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

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.

  • Query Design View: Birth Date field with parameterDecide 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.

  • Dialog: Enter Parameter Value - which birth date?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.

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing TablesTo subtopics
    Designing Queries Arrow: subtopic open
    Icon: StepSelect Query
    Icon: StepCalculated Values-Text
    Icon: StepCalculated Values-Numbers
    Icon: StepCalculated Values-Totals
    Icon: StepCalculated Values-Dates
    Icon: StepParameter Query
        Action Queries
    Icon: StepMake-Table Query
    Icon: StepAppend Query
    Icon: StepDelete Query
    Icon: StepUpdate Query
    Icon: StepCrosstab Query
    Icon: StepDocument a Query
    ExercisesTo subtopics

Project 4: Forms & Reports


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.

Query Design View: Parameter Query

Dialog: Parameter message

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!

Icon: TroubleProblem: 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.

Icon: Step-by-Step 

Step-by-Step: Parameter Query

 Icon: Step-by-Step

What you will learn:

to create a parameter query

Start with:  Class disk, 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. Icon: Save Save the query as QStaff-WhatCountry.

  3. In the Criteria row for the column Country/Region, type [What Country/Region?] and click OK.
     Query Design View: Parameter query - [What Country/Region?]

  4. Icon: Run Run the query. A dialog box appears.
     Dialog: Enter Parameter Value - Argentina

  5. Type Argentina in the text box.
    The datasheet lists only staff members in Argentina.
    Query Datasheet View: Parameter - Argentina

  6. Icon: Design Return to Query Design View

  7. Icon: Run Run the query again.

  8. Type USA in the text box and click OK.
    The datasheet lists only staff members in the USA.
    Query Datasheet View: Parameter - USA

  9. Icon: Design Return to Query Design View.

  10. Icon: Run Run the query again.

  11. Type United States in the text box and click OK.
    Query Datasheet View: Parameter - blank
    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. Icon: Design Return to Query Design View.

  13. Edit the criteria to read:
    [Which Country/Region: Argentina, Australia, USA?]
     Query Design View: Parameter - list of countries

  14. Icon: Run Run the query.
    Dialog: Enter Parameter Value - Australia
    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.
    Query Datasheet View: Parameter - Australia

  16. Icon: Save Save the query.  (QStaff-WhatCountry)