Adding wildcard characters to an Access query’s criteria is perfect for those times when you want to filter your data based on a common pattern rather than an exact text match. In this video, database expert Adam Wilbert shows how to incorporate the asterisk (*), question mark (?), and character set wildcards to match patterns found in the dataset.
- [Instructor] Adding wildcard characters to your query criteria is perfect for those times when you want to filter your data based off of a common pattern rather than an exact text match. Let's take a look at our guests here, and create a new query based off of them. I'll come up here to the create tab, and then click on query design. We're gonna pull in just the guests table, double-click on it to enclose the show table window. And the only data I wanna look at is the first name and the last name of our guests. Now previously, we saw that we can add in a criteria here for an exact text match. If I wanted to find all of the guests with the last name of Miller, I would just type in Miller here.
When I press enter, Access wraps that in double quotation marks because we're exactly matching the text, Miller. I'll go ahead and view the data sheet, and we'll see that we have a total of three guests that have the last name Miller. But what if we wanted to see all the guests that have the last name that just starts with an M? Let's go back into our design view, and we'll make that change down here in the criteria section. We can accomplish this with something called a wildcard character, and the most common wildcard character that you'll probably use is the asterisk character, which is shift eight on your keyboard. The asterisk character is a placeholder for any character and any number of characters.
So we can use it to specify a criteria that displays all of our guests with a last name that starts with M, by simply typing in M asterisk. This time when I press enter, Access wraps this in the double quotation marks like normal, but we're not exactly matching the text, M asterisk, we want something that's like M asterisk. So Access actually uses this like operator here. Now, if this is getting hard to see, what we could do is just right-click in this box, and choose something called the zoom window. That'll open up our text in the zoom window, and I can change the font to something larger that might be a little bit easier to see. We'll just switch it to 18 here.
So here it has like, then we have a double quote, M asterisk, and then a closing double quote. Say OK to that, and we can view the data sheet, and here we can see that we have a total of 25 guests whose last name all starts with the letter M. Let's go back into design view again. So the asterisk character will match any number of characters, but we can also use the question mark to signify a single character. So if I wanted to find all the people here with a last name that starts with M, but has a total of six letters in their name, I can add in five question marks here instead of the asterisk. Now if I press the data sheet view to view the results, we can see we have a total of 18 guests that have six letters in their name that starts with an M.
Once again, we'll go back to design view and change it again. The last wildcard that we can use is called a character list, and this is simply a list of characters that we wanna match inside of square brackets. So if I wanted to find all of the people with a last name that started with an A or B or a C, what we can do is, and I'll just open this up in the zoom window so we can see. What we could do is wrap those characters inside of square brackets inside of our quotation marks. So I'll type in an opening square bracket, A-B-C, a closing bracket, and then our asterisk character. So what this is actually going to do is match everybody that is like A asterisk, B asterisk, and C asterisk all together.
Say OK to that, and then view the data sheet. Now we can see a total that we have down here at the bottom after a brief calculation, we'll see that we have a total of 35 guests that match that pattern, and we can see that they all have a last name that either starts with A, a B, or a C. So adding wildcard characters to your query criteria can make filtering very efficient when there's a pattern to the records that you'd like returned. Simply use the asterisk to specify any number of characters, the question mark when you only want one character, or a character list inside of square brackets when you wanna match specific characters.
- 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.