Use Like criterion to locate data

Access for Microsoft 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007

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:

    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.

    Like query criteria

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

Like query results

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

If your database has a match
you’ll see
If your database doesn’t have a match
you’ll see
Multiple charactersa*aaa, aBa, aBBBaaBC
*ab*abc, AABB, XabaZb, bac
Special charactera[*]aa*aAaa
Multiple charactersab*abcdefg, abccab, aab
Single charactera?aaaa, a3a, aBaaBBBa
Single digita#aa0a, a1a, a2aaaa, a10a
Range of characters[a-z]f, p, j2, &
Outside a range[!a-z]9, &, %b, a
Not a digit[!0-9]A, a, &, ~0, 1, 9
Combineda[!b-m]#An9, az0, a99abc, 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.

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

This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.

Leave a Reply

%d bloggers like this: