Using 'Like' for Multiple Values in Access Query
Using 'Like' for multiple values in access query is used when you wish to find multiple values in one field, this is usually a text field. It is often used in conjunction with the WildCard (*) function.
Let's run through an example.
Mechanic Appointment example
You have a database of appointments for car repairs, in the appointment notes section (text field), you wish to pull out all fields where the appointment notes have specific key words. You have decided that these key words you are looking for are 'supply', 'replace' and 'install'. The problem is these words are contained in the appointment notes section along with many other words. How do you get these specific words out and ignoring the other words.
Using the 'Like' function will enable you to pull out these multiple values in a query. You will need to use the 'Like' function along with the wildcard (*) and the 'or' argument. Let's put this to work. You can easily adapt this to your own requirements, just replace the specific key words with your own.
Tip
- If you are getting other words where your word is a part of a bigger word for example you wanted the word 'rash', but words like 'crash' are being included try adding a space at the beginning and end of the word you are looking for i.e. ' rash '
Building the query
Open your Microsoft Access database and create a query. My goto option is to choose Create > Query Design. I then add the table I want. In my example it will be 'tblAppointments'.
Note: You will see in the Add Tables section, I have chosen the 'Links' option as my table is a linked table not a local table. If your tables are included in your current database then then choose the Tables tab.
Multiple criteria for one field (or)
The example below is for multiple criteria for one field only. Scroll down to see how using 'Like' for multiple values in access query is done for criteria for more than one field.
I now have the table I want. To add the field you wish to include in the query, double click on the field name. This will automatically add it to the query. For my example I am using the field 'ApptNotes'.
Next: We want to add the criteria, this is where we will be using 'Like' for the multiple values in our access query.
Add the following to the criteria row:
Like "*supply*"
The * is the wild card. This tells the query to include anything before or after the keyword 'supply', but must include 'supply'.
Change 'supply' to your keyword.
Run your query to make sure you are getting the results you expect.
Tips
- It does not matter if you use upper or lowercase words in the criteria, it will pick up all.
- Do not cut and paste the criteria, but manually type it in. Sometimes Access will not recognise the talking marks in web or Word documents. You need to type it in manually.
- Having the singular option i.e. replace will also pick up replaces, but supply will not pick up supplies, so you may want to consider just using the first part of the word i.e. 'suppl', or list them both.
Finally, add in the remaining Keywords, using the Like and the * (wildcard) under the first criteria, add the second key word next to the or:, then add the third underneath.
Run your query to see if you are correctly, using 'Like' for multiple values in access query.
Multiple criteria for one field (and)
That has the 'or' sorted, but what happens if you want to find all the records that have "supply" and "install" in the ApptNotes field?
Your would then change the first criteria to:
Like "*supply*" And Like "*install*"
You can still add 'or' statements under the above criteria, if for example you wanted to find all the records that have "supply" and "install" or "replace" in the ApptNotes field
Multiple criteria for more than one field (And/Or)
You might say, well all the above is well and good but I want to see all those who have 'supply' and 'install' in the ApptNotes table and where the make is a 'Ford'.
You would do this by adding the 'Ford' criteria, on the same line as the first criteria, but under the 'Make' field. Having them on the same line, is the same as using the 'And' syntax.
But, what about if you wanted to see, all those that had 'supply' and 'install' in the ApptNotes table OR where the make is a 'Ford'. This is not so much tricky, but if you do not know this, you can get caught out. What you need to do, is not to include it in the same line as the original criteria, but the next (or:) line.
For example:
I could probably keep going forever with different criteria examples, but hopefully this will be a good start for you in using 'Like' for multiple values in access query.
