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,
Step2. To switch to Design View,
Step3. To start to save the query with a different name, on the Quick Access toolbar,
Step4. To name and save the query, type:
Step5. To specify the appropriate criteria, in the DeptName column,
Click the Criteria field,
type: [Enter Department] Enter
NOTE: The square brackets are re quired, and periods are not allowed when entering parameter criteria.
Step6. To view the query result,
Step7. To enter a Department name, type:
NOTE: The text is not case sensitive.
Step8. To return to Design View,
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:
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
Step4. To view the query result,
Step5. To enter a department, type:
Step6. To enter a rank, type:
Step7. To save the changes to the query, press:
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,
Step2. To position the cursor, to the left of the parameter criteria, in the DepartName column,
Click the Criteria field
Step3. If necessary, to ensure that the cursor is positioned at the beginning of the Criteria field, press:
Step4. To use the LIKE operator with your criteria, type:
NOTE: Adding the LIKE keyword is not case sensitive, i.e. "Like" is the same as "like." Access will change the case automatically.
Step5. To set wildcard criteria for the Rank column,
repeat steps 2 through 4
Step6. To view the query result,
Step7. In the dialog box, type:
Step8. In the dialog box, type:
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.
Step9. To return to Design View,
Step10. To save the changes, press:
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.
Step2. To start to save the query under a new name,
Step3. To name the new query, type:
Step4. To position the cursor,
Click anywhere in the Criteria row
Step5. To select and delete the criteria, press:
Control key+a, Delete key
Step6. To add the HireDate field, from the top panel, in tblFaculty,
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.
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
Step9. To view the query result,
Step10. To enter a start date, type:
Step11. To enter an end date, type:
Step12. To save the changes, press:
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, watch this videoabout 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.