Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
So far, we've seen lots of ways that you the query designer can control the records displayed in the query data sheet. But what if we wanted to setup a framework and allow the end user of our query to supply some of the input? For instance, what if we have a question that asks which employees live in the state of blank or blank is supplied by the end user at runtime? Access allows us to answer these kinds of questions with a parameter query. A parameter query allows you to create a query framework that will request some little bit of additional information every time it's viewed. This saves you from creating a separate query for each and every possible question that you or your end users might have.
Let's go into Access and we will create a query that answers this specific question. We will go up to the Create tab and we'll create a new query in Design view. Now, we are interested in finding out information about our employees and the states they are from. So we will take the Employees table, double-click on it, and the States table, double-click on that. Go ahead and close the Show Table window. I am going to rearrange these a little, so I can see all of the fields and we will add a couple of fields to our query. Double-click on FirstName, double-click on LastName, and we'll double-click on StateName.
Now, in previous movies we saw how we can use the criteria field to specify a specific state. For instance, if I was interested in all the employees that lived in state of Arizona, I could type-in Arizona for the criteria, run this uery, and Access returns the 3 employees that live in Arizona. So go ahead and switch back in our Design view and we will change this into a parameter query. A parameter query uses the square bracket and then a bit of text to prompt the user for what kind of information you're looking for. So for instance, I could write open square bracket, enter state, closing square bracket.
When I run this query we will get a box that pops up that prompts the user to enter the state. This time we will type in California. We have one employee that lives in the state of California. If I switch back to Design view and then run this query again, we could type in a different state. This time Florida. Go ahead and say OK and you can see that we have 6 employees that live in the state of Florida. Let's go back into Design view. Now, we can combine this parameter request with some of the logical operators that we saw earlier.
For instance, if I was interested in two different States, I could say (nter first state and on the or line I could say enter second state. Make sure you have enclosed everything in square brackets. When I run this query, Access will prompt me for one state. Let's say New York and it will prompt me for a second state. Let's say Vermont. Now, I can see that I have 7 employees that either live in New York or Vermont. Let's go back into Design view. I will go ahead and get rid of the second state and I will expand this field a little.
You could also use the Or statement on the single line. Enter first state or enter second state. This will give us the same query that we just ran. Let's go ahead and clear this out and I will just get rid of everything here. We can also use the Like operator and we see this in the earlier movie, but we can use this with a parameter request to request something like all the states that begin with the letter A. We will start with like. We will input our parameter request.
Enter state letter. End that with a square bracket. Now, we need to join this to our asterisk wildcard and we will see this "concatenates" ampersand use a little bit later, but here we will just type the ampersand and then asterisk. So this says we want to look for all the states that start with a letter and they're joined with any letters after that. Let's go ahead and run this. Access will prompt us to enter a letter. I am going to enter A and we will say OK, and Access will give us all of the results for the states that begin with the letter A. Parameter queries are a powerful way to add interactivity to your database.
By providing a basic framework, you can create a single query that serves up answers to lots of different questions. Letting your end users define their own questions can be a really powerful tool. It adds flexibility to your database and it could be a way to streamline your work or providing some level of future proofing. Parameter variables will allow your queries to serve up answers to questions that you didn't even consider during the design process and I think that's really cool. The Enter Parameter Values dialog box isn't the only way for your end users to define the variables in a query. In the next movie we will look at how you can use the data entry forms and buttons to hook into the variables we've just seen.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64741 Viewers
80 Video lessons · 124337 Viewers
52 Video lessons · 60269 Viewers
59 Video lessons · 46100 Viewers