Use Like criterion to locate data

The Like criteria or operator is used in a query to find data that matches a specific pattern. For example, in our database, we have a "Customers" table, like the one below, and we want to locate only the customers living in cities whose names start with "B". Here's how we'll create a query and use the Like criteria:

1. Open the Customers table:
   
   
   

2. On the Create tab, click Query Design.

3. In the Show Table dialog, click Add and the Customers table gets added to the query designer.

4. Close the Show Table dialog.

5. Double-click the Last Name and City fields to add them to the query design grid.

6. In the City field, add the "Like B*" criteria, and click Run.
   
   

The query results show only the customers from cities names starting with the letter "B".

To learn more about using criteria, see applying criteria to a query.

Top of Page

Using the Like operator in SQL syntax

If you prefer doing this in SQL (Structured Query Language) syntax, here's how:

1. Open the Customers table and on the Create tab, click Query Design.

2. On the Home tab, click View > SQL View and type the following syntax:
   

SELECT [Last Name], City FROM Customers WHERE City Like "B*";

1. Click Run.

2. Right-click the query tab, Save > Close.

For more information see Access SQL: basic concepts, vocabulary, and syntax and learn more about how to edit SQL statements to sharpen query results.

Top of Page

Examples of Like criteria patterns and results

The Like criteria or operator comes in handy while comparing a field value to a string expression. 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]###"

Here are some ways of using Like for different patterns:

Kind of match	Pattern	If your database has a match you'll see	If your database doesn't have a match you'll see
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

Examples of Like criteria with wildcard characters

The following table shows types of results when the Like criterion is used with a wildcard characters in a table that might contain data with the specific matching patterns.

Criteria	Result
Like "E#"	Returns items with just two characters where the first character is E and the second is a number.
Like "G?"	Returns items with only two characters where the first character is G.
Like "*16"	Returns items ending on 16.

See more examples of wildcard characters.

Top of Page