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.
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.
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.
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.
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.
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
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.