To prompt a user to enter specific criteria such as a date range, use a parameter query. In this video, learn how to create a query that can prompt users to enter date ranges and prompts for other available data in fields.
- [Instructor] If you need to ask the user for input, such as a date range or employee number before they run a query, then the parameter query is the solution. After watching this video, you'll know how to create a query that will prompt users for filtering information. Then when we run the query, you'll see that only their desired results are displayed. Each time the query is run, new parameters can be entered to filter the information in a different way. Let's jump right into our parameters query database and get started. We're working with out employee data table. I'll go ahead and close the table back and go to Create. And we'll go right into Query Design. Double click on the EE Data table to add it. And let's go ahead and bring down all of our fields. I'll click on Department, hold my shift key down. That selects all of them. I can then drag and drop them down onto the grid. Now, in the Department field, in the Criteria row, in square brackets I'll type Which Department? And you can put anything you'd like in here. And you can just put department or which department would you like to view information for. Let me show you what happens when we run the query. Here's the prompt that comes up, and here's Which Department?, that I just typed in. Let me put in Marketing and click OK, and here are the results just for the marketing department. Pretty cool. Let's save this query, EE Information. Let me go back into Design View and let's add another prompt. So we're already going to ask them which department they'd like to see. Let's go over to the Benefits column and in square brackets, we will type in Which Benefits? So our users will not only be able to drill down into just the information for a department, but now they'll be able to see that information by benefit. So let's give them a list of benefits that are available in the table. Medical. And we have dental. We have both dental and medical as a package. Hospitalization and Rx. Close the square brackets. That looks good. So now they'll be prompted for which department and for which benefit they'd like to view. And the prompts will occur in the order in which these fields are displayed in our query grid. So department will be first and then benefits, but we could flip those just by moving the column over to, in front of department. Okay, let's go ahead and run this query. So which department, marketing. And here's our second prompt. Which benefit, medical. Sounds good. Here are the five people who are in the marketing department who have medical insurance with us. Let's go back into Design View and I'm going to remove the two sets of criteria that we currently have and enter a prompt in the date of hire so we can look at employees who were hired just in a specific time frame. I'll select the Criteria and delete them. And go to Date of Hire. Now, to do a date range, we first type in the word Between. Put a space in, square brackets, and we'll use a start date. That's our first prompt. Let me increase this column. Put a space in and type the word and, open my square brackets, End Date. Close our square brackets. So they will enter a start date and an end date. Let's run the query. Our start date will be January 1st, 2018. Our end date will be December 31st, 2018. Perfect, here's out 10 employees who were hired in 2018. So now that you know how to use these great parameter queries, I'd like for you to practice creating these queries using your own databases. That will really prepare you for the Access Expert exam.
- Modifying the database structure
- Managing table relationships and keys
- Exporting data
- Managing records and fields
- Creating and running queries
- Configuring form controls
- Modifying form positioning
- Grouping report fields
- Formatting reports
- Taking the practice exam