Wildcard Search & Like
By Richard Rost
6 years ago
Wildcard Searches with the LIKE Keyword
Learn how to perform wildcard searches using the LIKE keyword and the special characters (*,?,#) in your Microsoft Access queries. In this example, I'll show you how to find email addresses at specific domains, like @gmail.com.
Xavier from Bury St Edmunds, England (a Gold Member) asks: I need to create a report to show all of my customers who have email addresses in a few specific domains like Gmail and Yahoo. How can I do that?
Members
I'll show you how to search in a list or range of characters, exclude specific characters, and search for the actual wildcard characters inside your strings.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Courses
Like & Wildcards Covered: https://599cd.com/ACB5
Links
What is a Query: https://599cd.com/Query
Everything About Queries: https://599cd.com/441
Query Criteria: https://599cd.com/Criteri
 Keywords
wildcard characters, like keyword, searching for special characters, search for email domains, using wildcards in queries, wildcard filter and searches, how to create a wildcard query in microsoft access, access wildcard query, like conditions
Intro In this video, I will show you how to perform wildcard searches in Microsoft Access queries using the LIKE keyword. We'll cover how to filter customer records by first names and email domains, including specific criteria for names that start or end with certain letters and finding emails from particular providers like Gmail or Yahoo. I will explain the difference between the asterisk, question mark, and number sign wildcards, show examples for both text and numeric fields, and demonstrate how to search for multiple conditions in your database.Transcript In this video, I'm going to show you how to do a wildcard search using the LIKE keyword.
Today's question comes from Xavier from Barry St Edmunds, England, one of my Gold members. Xavier asks, I need to create a report to show all of my customers who have email addresses and a few specific domains like Gmail and Yahoo. How can I do that?
Well Xavier, the easiest way to do that is to learn something called a wildcard search in a query using the LIKE keyword. Let me show you how it works.
This is my basic free blank customer template. You can find a copy of this on my website. There is a link to download it down below in the description below the video window.
In here, we've got our basic customer list and our customer form. I added a couple of different records in here for the purposes of this example, but all I did was go into the customer table and put a few extra people in here. I changed a few things just so you could see some of the keyword stuff working better.
I know you're searching for email domains and we'll get to that in a minute, but let me show you how to search with something a little easier first. Let's take a look at first name.
Let me shrink this table up so we can see the list of all of our customers here. Let's create a query down below here.
Go to Create - Query Design. Here's a blank query. If you don't know how to make queries, I have other videos on how to make queries with criteria in them. I'll put some links down below. If you've never seen this before, go watch that stuff first and come back here.
I'm going to add my customer table to my query and then close the tables pane. I'm going to resize this so it's down underneath the table here just so we can see everybody at the same time. Move that right like that. Bring this up a little bit. Perfect.
I'm going to save this real quick as my CustomerQ for query.
Let's bring first name and last name down into my query. Let's go ahead and run the query real quick. To see what we get, I'm going to open up the ribbon here by double clicking on Design and click on Run to run this query. That's what we got. We've got all of the customers showing up there. That's fine.
Let's go back to Design View.
Now, if I want to see just the first names that are the word Jim, I can come down here in Criteria and, inside of quotes, put the word Jim. Now when I run the query, I'll see just the first names that are equal to Jim.
But what if I want to see the names that start with the letter J? To do that, we have to learn the LIKE keyword and the wildcard characters. There are a bunch of them, but the two important ones are the star and the question mark.
The star or the asterisk says any number of characters at any position. The question mark says one character at a specific position. Then there's the hashtag or the pound symbol or the octothorpe, whatever you want to call it. That is one numeric character at a specific position. I almost never use this guy, it's pretty much always the first two.
If you want to see all of the customers whose first name starts with the letter J, come down here. Type in the LIKE keyword, and then in quotes, J star. Just like that. Show me all the first names that start with the letter J and then any number of characters after it. Now if I run this query, there you go. There's all the J names. You can see that Richard and Chris do not show up.
Now, let's say you want to see all of the names that start with the letter J and end with the letter S. Back to Design View. I can come down here now and say J star S. It has to start with a J, it can have any number of characters in the middle, and then end with an S. If I run the query, all I see is James. That's the only name that meets that criteria.
How about any name that ends in an S? Forget the J. You can do star S. Just like that. Any number of characters at the beginning, ending with an S. Those names are James and Chris.
This is how you would find your email domains that you want to look for, Xavier. Come into Design View here. Let's get rid of the criteria under first name, and then let's bring over email address. Now we can do criteria.
Now I do not have any Gmail, but I have Amicron and 599CD. Let's do LIKE "[email protected]". You want to make sure you get the @ in there so that they don't have amicron.com somewhere else, like in 444amicron.com. This makes sure it's that exact domain, but any username at amicron.com. Now when I run my query, there you go.
If you're searching for multiple items, you can use the OR rows. Remember, it's AND across or down. If you're looking for Yahoo and Gmail, for example, you could come down here and put the second domain there. In my case, I've got 599CD.
Let me cheat up here and change this to just 5CD.com. Now when I run this, click and run it. There you go. You can see the two domains that I'm looking for here.
That should answer your question, but let me show you a couple more tricks. Let's take a look at that single-character wildcard.
Go back to Design View. Get rid of email address. Let's go back to working with first names. It's just easier for now.
Let's use: the first character is a J, and the third character is an M. We have James and Jim. Let's see if we can get that.
Come down here. Type in LIKE "J?M". That should give us the first character J, any second character, and the third character M. Let's see what that gives us.
I am only seeing Jim. Why is that? I should be seeing James too. The third character is M. Well, there's more stuff after it. That M says that last position has to be M. J, and then the second position, any character, and then the last position M.
This is a tricky one. After that, make sure you put an asterisk if you want to find Jim or James. I used to always use this example in my classes to stump people. I used to have fun with this one.
That's what you have to do to make sure that you have any number of characters after that. If you want to see all names where the third character is M, go "? ? M star". Now you'll see the same names here, but if you had CIM, whatever.
Remember, question mark is exactly one character. The star is any number of characters. That third wildcard is just like the question mark, except it's for numbers - numeric values only.
Now it will work with both text and numeric fields, but the character that goes there has to be numeric. For example, here's our email. Let's go back to email. Let me get rid of this, and I'll bring email down here.
If you want to see anybody who's got, let's say, the first character of their domain is numeric - I know it's weird, just the example I'm going to use - we can say down here, LIKE "*@[#]*". Then you can put ".com" here if you want to, but I'll just close it up. Now if I run it, there you go. It just shows the email domains that start with a number.
You can use that for addresses too. Here you can see we've got one address that doesn't start with a number value.
So instead of email, let's throw address in there. I want to see just the addresses that start with a number. This would be LIKE "#*". The first character has to be numeric, and then after that any number of characters. There are your numeric addresses. That's great for finding addresses where you don't have a street number out there.
This will also work with numeric values too. I've got one in here called family size. Let's find it up here at the table. There's family size. I got 211.
If you want to say the first character has to be a one, you'd say LIKE "1*". The first character has to be a one, and any number of characters after it. There are my ones - my 11 and my 1. They're all numeric, so it's kind of silly to have to use the number sign here, but actually, if you do "1#" like that, you'll get just 11. Why? Because, remember, these are treated like the question mark, and this says the second character has to be numeric. See that? All kinds of little tricks you can do.
So Xavier, I hope that answers your question, and it shows you how to search for specific domain names in email addresses using a wildcard search. Again, I pretty much stick to the first two. I almost never use that third one.
Want to learn more about wildcard searches? I've got an extended cut for members only. In this extended cut, I'll show you how to match a specific set of characters. For example, J and then either an A or an E, and M. You can search for a set of characters, a range of characters. You can exclude a certain set of characters, same rules. You can match numeric characters only, such as 0 through 5 or 8 through 9.
You can find special characters. So if your field actually has a star, a question mark, a number sign, or brackets in it, I'll show you how to search for those, and lots more. That's an extended cut for members. You can find out more about becoming a member by clicking on the Join link down below the video.
How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. What is the purpose of the LIKE keyword in a query?
A. To perform mathematical calculations
B. To find exact matches only
C. To perform wildcard searches with flexible matching
D. To sort records alphabetically
Q2. Which wildcard character represents any number of characters in any position?
A. Question mark (?)
B. Number sign (#)
C. Ampersand (&)
D. Asterisk (*)
Q3. What does the question mark (?) wildcard represent?
A. Any number of characters at a specific position
B. One character at a specific position
C. Only numeric values at the end
D. No characters allowed
Q4. To find all first names that begin with "J" and end with "S", which criteria should you use?
A. LIKE "J?S"
B. LIKE "JS*"
C. LIKE "J*S"
D. LIKE "*JS"
Q5. If you want to search for email addresses from a domain like "amicron.com", which criteria should you use?
A. LIKE "amicron.com"
B. LIKE "*amicron.com"
C. LIKE "*@amicron.com"
D. LIKE "amicron@*"
Q6. How can you search for records matching more than one criteria (for example, two domains)?
A. Use AND on a single row
B. List all domains separated by commas
C. Use the OR rows in the query criteria
D. Use parentheses around each criteria
Q7. What does the number sign (#) wildcard match?
A. Any alphabetic character
B. Any special character
C. One numeric character at a specific position
D. Any single word
Q8. If you wanted to match names where the third character is "M", what criteria would you use?
A. LIKE "??M*"
B. LIKE "?M*"
C. LIKE "*M*"
D. LIKE "M??*"
Q9. Which wildcard should you use if you only want to match email addresses that start with a number after the @ symbol?
A. Question mark (?)
B. Asterisk (*)
C. Number sign (#)
D. Exclamation mark (!)
Q10. If you want to find addresses that do not begin with a street number, what will help you identify missing numbers?
A. LIKE "*Avenue"
B. LIKE "#*"
C. LIKE " [space]*"
D. Find records where the address does NOT start with a number
Q11. Which of the following wildcards is used most frequently in wildcard searches according to the video?
A. Asterisk (*) and question mark (?)
B. Asterisk (*) and exclamation mark (!)
C. Question mark (?) and percent sign (%)
D. Brackets ([ ]) and number sign (#)
Q12. If family sizes in a table might be 1, 11, or 211, and you want to find records where the first character is 1, which criteria would you use?
A. LIKE "*1"
B. LIKE "1*"
C. LIKE "?1*"
D. LIKE "#1*"
Answers: 1-C; 2-D; 3-B; 4-C; 5-C; 6-C; 7-C; 8-A; 9-C; 10-D; 11-A; 12-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone explains how to conduct a wildcard search in a query using the LIKE keyword, which is a helpful technique for filtering data based on partial matches or patterns.
A question came in about producing a report of customers whose email addresses are hosted by certain domains, such as Gmail or Yahoo. The best way to tackle this is by running a query with wildcard searches in Access, making use of the LIKE keyword.
Starting with a standard customer database template, I demonstrate the process using a sample customer table and a corresponding form. To illustrate, I adjusted a few records in the sample data for better demonstration of these wildcard techniques.
First, rather than jumping straight to searching for email domains, it is easier to begin by looking for first names with certain patterns. By viewing the complete customer list, I start off by creating a new query in Query Design view. If you are not yet familiar with the basics of queries and setting criteria, it is worth checking out my other instructional videos first.
Once the customer table is added to the query, I include fields for first and last names, and run the query to see all records. To filter for just the first names equal to 'Jim', I type 'Jim' in the Criteria row under First Name. This produces only the records for Jim.
However, if you are looking for all names starting with the letter J, you need to use the LIKE keyword and a wildcard. The main wildcards used in Access are the asterisk and the question mark. The asterisk represents any number of characters in any position, while the question mark stands for exactly one character in a specific spot. There is also a hash symbol, which acts as a wildcard for a single numeric character, but most people rarely use it.
For example, to find first names that start with J, use LIKE "J*" in the Criteria row. This retrieves every name beginning with J, regardless of the following letters. To refine the search for names that start with J and end in S, use LIKE "J*S". This shows only names meeting both criteria, such as James.
If you want to pull in all names ending in S, just type LIKE "*S". This finds names like James and Chris where S is the last letter.
Turning now to searching for email domains, remove any criteria under first name, and add the email address field to your query. To find customers using a specific domain, such as amicron.com, the criteria should read LIKE "*@amicron.com". The asterisk at the start means any username, and including the @ symbol ensures it looks for the domain properly, without being confused by domains appearing as part of another string.
Should you want to search for several domains at once, you can use additional criteria rows, which function as OR clauses in your query. For example, to pull results for both amicron.com and 5CD.com, add each as a separate LIKE "*@domain.com" in its own row.
Moving on to more advanced wildcard use, you can work with single characters using the question mark. For example, to find names where the first letter is J and the third letter is M, use LIKE "J?M*". This matches 'Jim' and any other name fitting that pattern. The question mark only substitutes one character, so if more characters follow, make sure to append the asterisk.
If you want to find all names where just the third character is M, use LIKE "??M*". This will bring up any name where the third spot is M, regardless of what comes before or after.
The hash symbol is a wildcard for exactly one numeric character. You can use it to search for addresses or other fields where a specific digit must appear. For example, to find email domains that start numerically after the @ symbol, you can use something like LIKE "*@[0-9]*", or apply it to street addresses when the first character must be a number.
You can also apply these wildcards to numeric fields directly. For example, if you have a field for family size and want to find records where the number starts with 1, LIKE "1*" will get you all entries starting with the digit 1. If you add a hash symbol after 1, like "1#", it will only match those where the first digit is 1 and the second character is another number, like 11.
These wildcard searches are useful for a wide range of situations, whether you are filtering text or numeric fields. In my experience, most needs are met with the asterisk and the question mark. The hash is there for more specialized scenarios.
For those who want a deeper understanding, I offer an extended cut for members where I cover more advanced wildcard techniques. In the extended cut, I explain how to match and exclude specific sets of characters, search within character ranges, match only certain numeric values, and find special characters within the text that may otherwise act as wildcards.
If you want to access the extended cut and other members-only content, you can find details about membership options on my website. Silver members and above also gain access to live sessions and additional perks.
All TechHelp videos will remain freely available for everyone to watch, so keep your questions coming and I will continue producing new tutorials.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Creating a query with the LIKE keyword
Wildcard character asterisk for multiple characters
Wildcard character question mark for a single character
Using LIKE to match first names starting with a specific letter
Using LIKE to match names starting and ending with specific letters
Using LIKE to match names ending with a specific letter
Using LIKE to search for specific email domains
Using OR rows to search for multiple criteria in queries
Using LIKE with wildcards to match specific character positions
Using the number sign wildcard for numeric characters
Using LIKE to find addresses starting with a number
Using LIKE to search numeric fields with patterns
Difference between the asterisk and question mark wildcards
Practical examples of wildcard usage in Access queries |
