6. MICROSOFT ACCESS QUERIES – ADVANCED
6.5: Parameter Query
If you need a query but the criteria will not be known until runtime you use a Parameter Query. A parameter query is one where, on the Criteria line for some field, one types square braces [ ] and inside the [ ] one types a prompt for the user who runs the query. When a user runs a parameterized query, MS Access will show the user the prompt and waits for the user to respond with a value for the parameter – MS Access replaces parameters with the user-supplied values just before it executes the query.
Example
Suppose a user in the University database needs a list of courses having a specific value for credit hours. The query below has a parameter in the criteria line for creditHours:
When the query is run, the query is temporarily suspended while the user is prompted with the message as given in the square braces [ ]. Once the user responds to the prompt, the running of the query continues with the value the user entered as the criteria value.
Exercises
1) Consider the University database:
-
- Create a query to list all courses in a department (for which the user supplies the department code).
- Create a query to list all course titles where the user supplies both the department code and the credit hours. Note that two separate criteria, each with their own parameter, must be specified.
2) Consider the Company database:
-
- Write a query to list the employees who manage a department where the department code is provided by the person running the query.
- Write a query to list all employees in some department where the department code is provided by the person running the query.
- Modify the employee data in the Company database so at least two employees have the same first and last names. Develop a query that lists all employees having a specific first name and last name that will be specified by the end user.
3) Consider the Genealogy database:
- Create a query with two parameters: a start date and an end date. The query will list all persons whose birth dates fall in the range from start date to end date.
4) Consider the Library database:
- Write a query to list books due on a specific date (a parameter).
- Write a query to list books written by a specific author (a parameter).
