Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
There may be times when you're running a query, but you don't know the exact spelling of a name or you want several variations of a match to appear. In both of these cases, you can take advantage of Wildcard Characters to represent the letters or numbers that may vary. Go to the Create ribbon and choose Query Design. Select the Customers table and close the table window. I am going to pull the bottom down so I can see more fields. Double-click on our Company field and then scroll down and double-click on the Notes field.
In the Company column, click in the Criteria line, and type asterisk the word deli and another asterisk. The asterisk represents a string of unknown letters, any number of them by putting in asterisk before and after the word deli. I am telling Access that deli can appear in the beginning, middle or end of the name. Run the query and I have three different matches. I'll go back to Design View. Notice that Access modified my criteria. It added the word Like and quotation marks around my search string.
Now let's vary this. What would happen if I would take out the first asterisk? This is telling Access that I want deli to be in the first part of the Company name and when I view the results, I have Delish Food. Now let's do the opposite? I'll put that asterisk back and I'll take out the asterisk at the end of the name, and now we have anything that ends in the word deli and I have two, Istria's Deli and Katz's Deli. Now, there are our additional wildcards too.
For example I have a customer called Avalon, but it's not spelled the normal way, and I can never get track. So I'll erase this Criteria and change it to Av?l?n. The question mark stands for just one letter, instead of a possible string. When I run it, my match gives me Avulon; with the u. Now one thing to watch out for is that if I am going to start my wildcard query with a question mark I have to use special syntax.
If I just typed in ?v?l?n, when I run the query, I am going to get an error message saying that the expression I entered contains invalid syntax. I'll click OK. So if I am going to use a question mark as the first character, I have to manually enter in the Like in the quotation marks myself. And when I view that, I'll get Avalon again. What happens when you're searching for text that includes one of these characters? In that case you have to use very specific code.
I'll erase my criteria from my company. Now in my database, I use my Notes field to track questions for my customer's and those questions end in question marks. So here's the code that I need in order to find any questions for my customers. I'll type in Like in quotation mark and I'll put in asterisk to start our text string match. Now I'll put in a square bracket to indicate that we are using a special character, the question mark that I'm looking for in my questions for my customers. I'll end the square bracket When I run this, now I have got two questions for my customers that I need to answer.
Now note that if I forget to put in the Like and the quotation marks and I just start with the criteria that has a bracket, they don't give me a parameter query. Parameter queries are covered in the Access 2007 Essential Training course. Now here are a few more wildcards that you might find useful. We've already seen the question mark for one character. You can also use a number sign if you specifically wanted to return a number. We have also seen the asterisk for text strings.
You can also combine these. For example, here I have a question mark then letter A and then an asterisk. This is saying I want to start with a new letter, but I want the second letter to be an A, and then anything at all can come after that, and I get the results Bat or batter. To bump it up a notch, I can also specify that I want the second letter to either be an A or an E. So I would have question mark square bracket ae square bracket, asterisk.
Sample matches would include Bat and batter like before, but also bet and better. You can also exclude characters. If I put in that same a and e in the brackets by putting exclamation mark in front of them, this is telling them that I want the second letter to be anything except an a or an e, and tell them I get Bit or bitter. Last, you can also search for a range of possible matches. Here I am telling it that I want my second letter to be in A, a B, a C, a D or an E. So possible matches might be Eat; where A is the second letter, able; B is the second letter, ace where C is the second letter, edit; where D is the second letter or feat; where E is the second letter.
Using wildcards will allow you to run queries with variables, exponentially increasing your ability to find matching records in your tables.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.