![totn Access](/static/U7wtV/static/picture/access.png?d=55c6ebd00&m=U7wtV)
MS Access 2003: LIKE condition (using wildcards)
This MSAccess tutorial explains how to create a query using the LIKE condition with wildcards in Access 2003 (with screenshots and step-by-step instructions).
See solution in other versions of Access:
The LIKE condition allows you to use wildcards in the where clause of a SQL statement in Access 2003/XP/2000/97. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.
The patterns that you can choose from are:
Wildcard | Explanation |
---|---|
* | Allows you to match any string of any length (including zero length) |
? | Allows you to match on a single character |
# | Allows you to match on a single numeric digit |
Example
Like 'b*'Result: all values that start with bLike '*b*'Result: all values that contain bLike '*b'Result: all values that end with bLike 'b?'Result: all values that start with b and are 2 characters in lengthLike 'b#'Result: all values that start with b and are 2 characters in length where the second character is a number
Here is an example of how you'd use the LIKE condition in a query:
In this example, we are looking for all company names that start with "b".
Combining the LIKE condition with the NOT operator
You can also combine the LIKE condition with the NOT operator.
Example
Not Like 'b*'Result: all values that do not start with bNot Like '*b*'Result: all values that do not contain bNot Like '*b'Result: all values that do not end with bNot Like 'b?'Result: all values that are not 2 characters in length starting with bNot Like 'b#'Result: all values that are not 2 characters in length that start with b and where the second character is a number
Here is an example of how you'd use the NOT LIKE condition in a query:
In this example, we are looking for all company names that do not start with "b".
Frequently Asked Questions
Question: In Microsoft Access, I'm trying to create a query that returns phone numbers, but excludes any phone numbers that start with 713612, 713312, or 281999. How can I do this?
Answer: You can do this by using the NOT LIKE condition.
For example, we have a table called Sample that contains phone numbers within a field called PhNo. We can write a query using the NOT LIKE condition as follows:
The SQL for the query above is:
SELECT Sample.PhNo FROM SampleWHERE (Sample.PhNo) Not Like '713612*'And (Sample.PhNo) Not Like '713312*'And (Sample.PhNo) Not Like '281999*';
By separating the NOT LIKE conditions with the AND operator, we are able to exclude all three types of phone numbers from our results.
Additional Information
Click here to find an interesting solution using the LIKE condition to exclude records that contain a wildcard character.
Advertisement