Microsoft Access queries can be made more flexible with the addition of a parameter request. In this video tutorial, database expert Adam Wilbert shows how to create a query framework that asks the end user for the criteria that they’re interested in at runtime, rather than hard coding fixed criteria into the query design.
- [Narrator] Sometimes you'll want to create a query framework that answers many variations on the same question. Instead of hard coding your queries to use the exact same criteria every time they're run, like what we've been doing, you can instead ask the users for the criteria when the query is run. This is called a parameter request, and they're another very useful tool to have when building your databases. Let's start by creating another query here in design view, and this one's gonna be pretty simple, we're just going to take a look at our guest data. From the guest table, I want to see the first name, the last name, and finally the country that the guest is from.
Now, we've seen how to specify criteria that looks at a single country. For instance, if I come down here into the criteria row, underneath country, I can just type in France. And if I view this datasheet, we'll see all of our French guests. We have a total of five of them. Let's go back into design view. So instead of hard coding a query to always look for France, what we can do is ask our user what country they want to see when a query is run. To do that, we'll type instead a square bracket and then a prompt for them to enter in some data. I'll type in, which country would you like to see? We'll finish that with a closing square bracket.
Now I'm gonna go ahead and make this a little bit wider so we can see the entire text here, and there it is. So the text between the square brackets is going to be a prompt in a text box. Let's go ahead and run this query. And here we get our inter-parameter value text box, Which country would you like to see? This time I'm gonna type in Russia. I'll say okay. And here we can see that we have a total of seven guests from Russia. We can either close the query and run it again, or I can come up to the top, and press the refresh all button. It's gonna prompt me one more time for another country. This time I'll type in Indonesia.
And press okay, and I can see that I have a total of 19 guests from Indonesia. So this is a very simple and flexible way to run multiple types of queries. We can also combine parameter requests with some of the other criteria tricks that we've seen earlier in the course. Let's go back into design view, and I'm gonna change this a little bit. I'm gonna get rid of the criteria request here, underneath country, and take a look at our last names again. Earlier we saw that we can include wild card characters to look for people with just a specific last name. For instance if I type in M star, we'll look for all the people that have a last name that starts with the letter M.
Instead of hard coding that M in there, we can have a parameter request ask the end user what letter they'd like to see. We'll do that using the same like operator at the beginning. And then, inside of square brackets, enter first character of the last name. I'll finish it with the closing square bracket, and now we need to join it to our wild card character, that asterisk character. We'll do that with the ampersand, which is the concatenation, it's the joining text symbol that we use here inside of Access.
So we're gonna join whatever the user types into the parameter request box to the asterisk, and that needs to go inside of double quotes. I'll type double quote, asterisk, and then a closing double quote. Once again, let me just make this a little bit wider so we can see the full text, and there it is. So now let's go ahead and run this. And it's going to ask me for the first character of the last name that I'm interested in. Let's just go ahead and type a B. And what this is going to do is take this B, put it down here inside this box, and say like B asterisk and return all the guests that have a last name that starts with a B.
Once again, I can press refresh all, type in another letter, type G this time, and this time we see all the guests with a last name that starts with G. Now this is a handy query to have, so let's go ahead and save this one to the database so we can use it later. I'll press control S on the keyboard and name it guests by last name. That'll go ahead and save it down here in our navigation pane. So parameter requests add another level of customization to your database processes, and allow you to quickly build a query framework that can answer an infinite number of questions.
Whatever we type into the parameter request box gets populated into criteria field and allows us to create one query that answers lots of questions, rather than creating many queries that only answer one question each.
- Determine the essential uses for the Trust Center.
- Explore the functions of the database Navigation pane.
- Recognize the fundamentals of entering data when using Access.
- Identify the necessary steps when importing a table when using Access.
- Break down the fundamentals of filtering and sorting table data in Access.
- Identify the method utilized when building queries in Design view.
- Determine the role of forms in Access.
- Examine all of the elements involved in maintaining a database in Access.
- Explore how to properly protect an Access database with a password.