Like Operator

Used to compare two string expressions in Access Basic or in an expression.

Syntax

Access Basic: result = expression Like pattern

Expression: Like "pattern"

Notes

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 like those recognized by the operating system to find a range of values (for example, Like "Sm*").

In an expression, you can use the Like operator to compare a field value to a string expression. For example, if you type Like "C*" in the Criteria row of the QBE grid, 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. Suppose you type the following expression in the Criteria row of the QBE grid:

Like [Enter the first letters of the name:]&"*"

When the query is run, a dialog box prompts the user with "Enter the first few letters of the name". If the user types SM in the dialog box, the query looks for the pattern SM* (that is, all names beginning with the letters SM).

You can also use Like in an expression as a setting for the ValidationRule property or as a macro condition. The following example returns data that begins with the letter P followed by any letter between A and F and three digits:

Like "P[A-F]###"

To refer to the value of a control, enclose the control identifier in quotation marks (" ") and vertical bars (| |). For example, suppose you set the criteria for an Art Order report in a form called Print Art Order. By entering a value in the Order ID field, you can print either a batch of art orders or a specific art order. In the query that the report is based on, you set the criteria for the Order ID field like this:

Like "|[Forms]![Print Art Order]![Order ID]|"

If you don't include the vertical bars, the Like operator returns the name of the control rather than its value.

In Access Basic, if expression matches pattern, result is True (-1); if there is no match, result is False (0); and if either expression or pattern is a Null, result is also a Null. The case sensitivity and character sort order of the Like operator depend on the setting of the Option Compare statement. However, if the module doesn't contain an Option Compare statement, the default string-comparison method is Binary.

Examples

This example can be used in a criteria expression in the Criteria row of a query or filter. It returns a list of customers whose names begin with the letter A through D, in alphabetic order.

Like "[A-D]*"

The following example uses the Like operator to compare two strings.

Result = "aa" Like "a[*]a"

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"
Special character	"a[*]a"	"a*a"	"aaa"
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"