Using Parameter Queries

In previous examples, you have seen how to enter criteria directly in the query design grid to limit a query's results. Parameter queries provide a more dynamic way to limit results. They prompt the user for the criteria at the time the query is run. The results will match that of the specified criteria.

Creating a Parameter Query

Suppose we want to limit query results to faculty of a specific department. We could get the desired information by entering criteria in the Criteria field of the query design grid. However, the query would be more flexible if we could enter criteria at the time of execution.

Setting a Single Parameter

We already have a query that lists all faculty by department. We will begin by modifying that query to include parameter criteria so the user can specify a department as they run the query.

Step1. To open the query,

Double-Click qryAllFacultyByDept

You see 124 records.

Step2. To switch to Design View,

Click

Step3. To start to save the query with a different name, on the Quick Access toolbar,

Click

Step4. To name and save the query, type:

qryFacultyDeptRankParams Enter key

The query is saved with the new name, which is displayed on the tab.

Step5. To specify the appropriate criteria, in the DeptName column,

Click the Criteria field, type: [Enter Department] Enter key

NOTE: The square brackets are required, and periods are not allowed when entering parameter criteria.

Step6. To view the query result,

Click

You see the Enter Parameter Value dialog box:

Step7. To enter a Department name, type:

Biology Enter key

NOTE: The text is not case sensitive.

You see a list of the 34 faculty in the Biology department.

Step8. To return to Design View,

Click

Step9. To run this query specifying other departments such as Mathematics or Philosophy,

repeat steps 5 through 7 for other departments

Step10. To save the changes we made to the query, press:

Control key + S

The changes we have made are saved. Now let's create a query using multiple parameters.

Using Multiple Parameters

Suppose we want to see Biology faculty of a certain rank. We will have two criteria parameters. We can place expressions in multiple fields or place multiple expressions in the same field. Access will prompt the user for the first criteria in the query design grid, then prompt for the second criteria. The query then returns data that matches the specified criteria.

Let's make changes to the design of qryFacultyDeptRankParams to allow for an additional parameter.

Step1. Return to Design View, if necessary,

Step2. To begin adding a second parameter, in the Rank column,

Click the Criteria field

Step3. To enter a parameter criteria function, type:

[Enter Rank] Enter key

Step4. To view the query result,

Click

The parameters are displayed from left to right so the first prompt is for the department.

Step5. To enter a department, type:

Mathematics Enter key

We are prompted to specify a rank.

Step6. To enter a rank, type:

Professor Enter key

The query returns the 7 records of Mathematics faculty who are professors. We can use this query at any time to look for faculty from a certain department and of a specific rank.

Step7. To save the changes to the query, press:

Control key + S

The changes are saved.

Using Wildcards in Parameter Queries

Earlier we used the asterisk wildcard in the Criteria field to create more flexible criteria. We saw how to narrow the results to those that meet on a certain day of the week or don't meet on a certain day of the week.

In our example, let's say that we are looking for faculty of a specific department but we don't want to type the whole department name. Because wildcards behave differently when used with parameter criteria, we cannot simply substitute an asterisk for the department. Access would treat the asterisk literally and would search for records where the department included an asterisk.

Access does provide syntax for enabling the use of wildcards in parameter queries. Let's see how to modify the expression in the Criteria field to allow the use of wildcards in the parameter query.

Step1. To return to Design View,

Click

Step2. To position the cursor, to the left of the parameter criteria, in the DepartName column,

Click the Criteria field

We see the cursor blinking to the immediate left of the criteria.

Step3. If necessary, to ensure that the cursor is positioned at the beginning of the Criteria field, press:

Home key

Step4. To use the LIKE operator with your criteria, type:

Like Enter key

NOTE: Adding the LIKE keyword is not case sensitive, i.e. "Like" is the same as "like." Access will change the case automatically.

The parameter criteria reads: Like [Enter Department].

Step5. To set wildcard criteria for the Rank column,

repeat steps 2 through 4

The parameter criteria in the Rank column should read: Like [Enter Rank].

Now when the query runs, we won't have to enter the full value for either department or rank; we can put in just the beginning of the value.

Step6. To view the query result,

Click

We see a dialog box telling us to "Enter Department." Let's look for departments that start with the letter "p."

Step7. In the dialog box, type:

p* Enter key

We see a dialog box asking us to "Enter Rank." Let's look for ranks that start with the letters "as."

Step8. In the dialog box, type:

as* Enter key

NOTE: You can also mix and match when using the asterisk. For example, you can look for all of the Mathematics faculty by entering "Mathematics" in the Department prompt and by entering an asterisk in the Rank prompt.

NOTE: The * wildcard character will not return null values (that is, empty fields). To see null values in a parameter criteria query, in Design View, add the text Or Is Null after the parameter. For example: Like [Enter the Rank] Or Is Null.

We see the 36 records for the associate professors and assistant professors in the Philosophy and Political Science departments.

Step9. To return to Design View,

Click

Step10. To save the changes, press:

Control key + S

The changes are saved.

Using Multiple Parameter Criteria on a Single Field

Queries can be used to search for data where the criteria cover a range of information, like a specific time period or price range. When we want to specify a range, we can combine parameter expressions with operators like this:

>=[Enter a start date] And <[Enter an end date]

Exiting code block.

In this case >=, <, and And are operators.

Let's try using this method to search for faculty hired within a certain date range.

Step1. Return to Design View, if necessary.

As we are going to remove the previous criteria, let's save this as a new query.

Step2. To start to save the query under a new name,

Click

Step3. To name the new query, type:

qryFacultyHireDateRangeParam Enter key

The question we now want to ask deals with Faculty hire date considerations. Since we don't need the previous criteria for the Department and Rank columns, we will delete them before we add the criteria to the HireDate column.

While we could select and delete each of the existing criteria, since we want to remove all the current criteria, let's use a short cut.

Step4. To position the cursor,

Point to left side of first Criteria column until the cursor turns into a right-pointing arrow

Now that our cursor is positioned, all we need to do is select the data and delete it.

Step5. To select and delete the criteria, with the right-pointing arrow cursor displayed,

Click the left side of the Criteria row, press: Delete key

The Criteria fields for all columns are cleared. Next we will add the HireDate to the query and add criteria to it.

Step6. To add the HireDate field, from the top panel, in tblFaculty,

Double-Click HireDate

While we could type in the Criteria field, the small space limits our ability to see the entire criteria as we type. Access provides the Zoom tool to allow a larger window for viewing the cell's contents.

Step7. To open the Zoom window,

Right-Click the HireDate Criteria field, Click Zoom...

NOTE: If Zoom is grayed out, click another field and then right-click the Criteria row of the HireDate column again.

The Zoom window opens, providing a larger work space for typing the criteria.

Step8. To create a parameter query to view faculty hired in a specific date range, type:

>=[Enter a start date] And <[Enter an end date] Enter key

Step9. To view the query result,

Click

We see the Enter Parameter Value dialog box.

Step10. To enter a start date, type:

1/1/2001 Enter key

Step11. To enter an end date, type:

1/1/2006 Enter key

We see the 21 members of faculty who were hired between 1/1/2001 and 1/1/2006. Let's save the changes to this query.

Step12. To save the changes, press:

Control key + S

Step13. Close the query.

Adding Parameters to a Query

Let's add parameters to another existing query to allow us to see the professors teaching courses in a specified semester.

This section has less guidance than the previous sections. If you would like a walk-through of this section, watch the video Adding Parameters to a Query.

Step1. Open qryCoursesSectionsAndAllFacultySummer18.

Step2. Switch to Design View.

Step3. Save it as "qryCoursesSectionsAndAllFacultySemesterParameter."

Step4. Add the required parameter to the SemesterYear field.

NOTE: If you keep the Or is Null, which is part of the current criteria for SemesterYear, the results will include the faculty for the semester entered and all faculty who are not assigned to teach in any semester. This also means the query can be run with no parameter value entered, which will only show faculty who aren't assigned to teach in any semester.

Step5. Save and test the query.

Step6. Close the query.

NOTE: To view a solution for this exercise, see Solution: Adding Parameters to a Query.