Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101


Home > Jan's CompLit 101 > Working with Databases > Tables & Queries > Queries> Parameter Query
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

Tables & Queries: 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 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.

How To Create a Parameter Query

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

  1. Query Design View: Birth Date field with parameterDecide on the field or fields for which you want to use parameters.
  2. 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. Consider whether you need to show an example of the format to use. Some symbols are not allowed in the Criteria row, like ! and `.
  3. Dialog: Enter Parameter Value - which birth date?Run the query and a Parameter dialog opens.
  4. Enter an appropriate value in the parameter dialog.
  5. 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.


Example: Parameter Query

from LanguageArtsClasses.accdb

Example: Query without parameter - Enter criteria each time (Access 2016)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.

Query Design View: Parameter QueryThe 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#:]

Dialog: Parameter messageWhen 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!


Icon: TroubleProblem: Unexpected Parameter Dialog.

Dialog: Enter Parameter - [Name]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.


Icon: Step-by-Step 

Step-by-Step: Parameter Query

 Icon: Step-by-Step

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: Class disk, Projects database from previous lesson 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. Icon: Back Up diskBack 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.

    Icon: Access 2007Access 2007: Button: Office > Manage > Back Up Database

    Icon: Access 2010 Access 2010: File > Save and Publish > Back Up Database > Save As button

    Icon: Access 2013 Icon: Access 2016 Access 2013, 2016: File > Save As > Back Up Database > Save As button

  2. Icon: Design View 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.

  3. Button: Save (Access 2010) Save Object As QStaff-WhatCountry.

  4. Query Design View: Parameter query - [What Country/Region?]

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

  5. Icon: Run Run the query.
    A dialog box appears.
     

  6. Dialog: Enter Parameter Value - ArgentinaType Argentina in the dialog box and click on OK.
    Icon: Datasheet View The datasheet lists only staff members in Argentina.

    Query Datasheet View: Parameter - Argentina

  7. Icon: Design View Return to Query Design View

  8. Dialog: Enter Parameter Value - USA

    Icon: Run Run the query again.

  9. Type USA in the dialog box and click OK.
    Icon: Datasheet View The datasheet lists only staff members in the USA.

    Query Datasheet View: Parameter - USA

  10. Icon: Design View Return to Query Design View.

  11. Dialog: Enter Parameter Value - United States

    Icon: Run Run the query again.

  12. Type United States in the dialog 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. The user needs some help with what country names will work.

  13. Icon: Design View Return to Query Design View.

  14. Query Design View: Parameter - list of countries

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

  15. Dialog: Enter Parameter Value - Australia Icon: Run Run the query.

    The dialog now lists the possible choices. This works well for short lists.

  16. Type australia into the text box and click OK.
    Icon: Datasheet View There is only one staff member in the datasheet.
    You did not have to capitalize correctly. Sweet!

    Query Datasheet View: Parameter - Australia

  17. Button: Save (Access 2010) Save and close the query. 
    [QStaff-WhatCountry]


Criteria with Two Parameters: Between

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.

  1. Icon: Design View Create a new query based on the table Projects.
  2. Design View: Parameter query - BudgetsIn Query Design View, drag the * field to the grid and then also the field Budget.
  3. Uncheck the Show box for Budget.
    It is already included because of the * field, which brings in all of the fields from the table.
  4. 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.

  5. Icon: Run Run the query.
    The first parameter dialog appears.

  6. Parameter Dialog: Maximum Budget = 2000 (Access 2010)Parameter dialog: Minimum Budget = 100 (Access 2010)Type 100 and click on OK.
    The second parameter dialog appears.
  7. Type 2000 and click on OK.

    Datasheet View: Project parameter query (Access 2010)Icon: Datasheet View The query runs and shows a datasheet with 5 records. The Projects table currently has 7 records.

  8. Icon: Run 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.

    Message: The expression is typed incorrectly... (Access 2010)


Set Data Type for Parameters

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.

  1. Icon: Design View Return to Query Design View
  2. On the Query Tools: Design ribbon tab, click the Parameters button Button: Parameters (Access 2010).
    A new dialog appears with blank entries.
  3. Dialog: Query Parameters (Access 2010)In the first blank line type the name of the first parameter in the query, [Minimum Budget]
    Be sure to include the square brackets.
  4. In the Data Type column for the first row, select Integer from the drop list.
    Now Access will expect you to enter a number, without decimals.
  5. In the second blank line type the name of the second parameter in the query, [Maximum Budget]
  6. In the Data Type column for the second row, select Integer from the drop list.
  7. Click on OK to close the dialog.
    You are back in the Query Design View.
  8. Message: The value is not a valid value for this field. (Access 2010)

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

  9. Icon: Experiment Experiment: Data Types
    Try different values, like text, dates in different formats, and various large and small numbers to see what is accepted and what error messages you see.
  10. Button: Save (Access 2010) Save the query with the name QProjects-BudgetsBetween and close the query.