On a roll with the Microsoft Access articles, check out the new one on using the SQL statement LIKE for a quick guide to using the LIKE statement in Access.

This is coming straight off a problem I helped a co-worker with today. They were running some simple queries in Access, however they couldn’t nail down one problem: How to retrieve only those records from a table where the first part of their query field equaled the value “yes”. Their problem was that they had other information in the field beside the “yes”, but that’s all they wanted to query on.

The solution to this is very simple, and it comes in the form of the LIKE statement. This operator allows you to find all the records in a table where the value in your specified field matches the Regular Expression you provide. The detailed definition of Regular Expression can be found here. In short, it is basically a pattern that Access will match against the value in a field.

For example, the Regular Expression ‘yes*yes’, would match any values where the first three characters are “yes” and the last three characters are “yes”. The “*” specifies that any other characters can be between the two “yes” blocks. Any time you want to specify “any characters” in Access, you can use the “*”.

The syntax for the LIKE statement is as follows:

field LIKE ‘Regular Expression’

To break this down:

• field - The field to which you will be selecting the records on, and which the Regular Expression will be matched to.
• LIKE - The operator.
• Regular Expression - As described above, the pattern you want to evaluate the field on.

As an example, suppose I want to select all of the records in my table where the “CustomerName” field begins with the letter “A”. That statement would look like this:

SELECT * FROM CustomerName LIKE ‘A*’

It is also important to note that LIKE statement when used in Access is case insensitive. Using the above example, any value that began with “A” or “a” would be retrieved.

Those are basics of using the LIKE statement. I think you will find it increasingly useful as your skill with Regular Expressions increases.