Using the LIKE Operator and Wildcards to Match Patterns Between Strings

When we enter a criteria for a text field in an Access Query, the results can be a little limiting when we use the equality operator (ie = ).  So, for example, if we have a list of employees and we want to search for all Sales Representatives, we could use ="Sales Representative" as the criteria.  This would correctly produce a list of employees containing this exact value in the Position field.  This is all fine and good.  However, what if we wanted a list of all employees that have the word sales appearing at the start of this field?  Take a look at this screen shot of the table in question:

Figure 1: The Employees Table

As you can see, there are three different job titles beginning with the word Sales - there is Sales Manager, Sales Representative, and Sales Assistant.  Despite this fact,  if we were to enter = "sales" as the criteria, no records would be returned in the query results.  This is because it is not an exact match for any job title.

This is where the LIKE operator comes in handy.  The LIKE operator is used in conjunction with one of the Access Wildcard symbols to compare a pattern between two strings.  So instead of entering the criteria = "sales" we could enter LIKE "sales*".

Figure 2: Query Criteria using the LIKE operator.

This latter criteria would return all records beginning with the word "sales".  So any word letter or character coming after "sales" is ignored.  As such, our query now returns records containing any of the three above mentioned job titles in the Position field.

Figure 3:Query results returning all three job titles
beginning with the word "sales"... 

The type and position of Wildcard is crucial in all this.  We used the * wildcard symbol indicating that it is representing any number of characters (including zero). This is in contrast to the ? wildcard symbol which just represents a single character (eg LIKE "Sales Representativ?" to return "Sales Representative"), or the # wildcard symbol representing a single digit from 0 to 9 (eg LIKE "Person#" to return "Person1".  We could also specify a range of characters between square brackets - for example LIKE "Person[1-5]" would return the string "Person1", or "Person2"etc, but not "Person6" or above.  

These wildcard symbols can appear anywhere in the search string.  We placed ours after at the end of the search string to return any string beginning with "Sales ...".  Had we wanted to return any job title ending in "... Assistant", we would have placed the wildcard at the beginning of the search string ie LIKE "*Assistant" . There are even scenarios where a wildcard may be used in a specific place in the middle of a string too.  Try experimenting with all this - it's the best way to learn!