Like operator (Microsoft Access SQL)
Applies to: Access 2013 | Access 2016
Compares a string expression to a pattern in an SQL expression.
expression Like "pattern"
The Like operator syntax has these parts:
|expression||SQL expression used in a WHERE clause.|
|pattern||String or character string literal against which expression is compared.|
You can use the Like operator to find values in a field that match the pattern you specify. For pattern, you can specify the complete value (for example,
Like "Smith"), or you can use wildcard characters to find a range of values (for example, ), or you can use wildcard characters to find a range of values (for example,
In an expression, you can use the Like operator to compare a field value to a string expression. For example, if you enter
Like "C*" in an SQL query, the query returns all field values beginning with the letter C. In a parameter query, you can prompt the user for a pattern to search for.
The following example returns data that begins with the letter P followed by any letter between A and F and three digits:
The following table shows how you can use Like to test expressions for different patterns.
|Kind of match||Pattern||Match (returns True)||No match (returns False)|
|Multiple characters||a*a||aa, aBa, aBBBa||aBC|
|ab||abc, AABB, Xab||aZb, bac|
|Multiple characters||ab*||abcdefg, abc||cab, aab|
|Single character||a?a||aaa, a3a, aBa||aBBBa|
|Single digit||a#a||a0a, a1a, a2a||aaa, a10a|
|Range of characters||[a-z]||f, p, j||2, &|
|Outside a range||[!a-z]||9, &, %||b, a|
|Not a digit||[!0-9]||A, a, &, ~||0, 1, 9|
|Combined||a[!b-m]#||An9, az0, a99||abc, aj0|
This example returns a list of employees whose names begin with the letters A through D. It calls the EnumFields procedure, which you can find in the SELECT statement example.
Sub LikeX() Dim dbs As Database, rst As Recordset ' Modify this line to include the path to Northwind ' on your computer. Set dbs = OpenDatabase("Northwind.mdb") ' Return a list of employees whose names begin with ' the letters A through D. Set rst = dbs.OpenRecordset("SELECT LastName," _ & " FirstName FROM Employees" _ & " WHERE LastName Like '[A-D]*';") ' Populate the Recordset. rst.MoveLast ' Call EnumFields to print the contents of the ' Recordset. Pass the Recordset object and desired ' field width. EnumFields rst, 15 dbs.Close End Sub
- Access for developers forum
- Access help on support.office.com
- Access forums on UtterAccess
- Access developer and VBA programming help center (FMS)
- Access posts on StackOverflow
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.