ACC: How to Use the LIKE Operator in Parameter Queries

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

This article describes how to search for all the records in a table that begin with a certain letter.

MORE INFORMATION

An easy way to search for records that begin with a certain letter is to create a parameter query that prompts for a character to search for. The LIKE operator can be used with the wildcard symbol (*) to accomplish this task. This example uses the Employees table in the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access version 2.0 or earlier).

Method to Create Parameter Query Using Like Operator

1. Create a new query based on the Employees table.

2. For the LastName field, place the following line in the Criteria box for the field:

         LIKE [Enter the first char to search by: ] & "*"
   

         -or-
   

         LIKE "*" & [Enter any char to search by: ] & "*"
   

         NOTE: There is a space in the Last Name field in Microsoft Access
         2.0 and earlier.
   

         NOTE: The Parameters dialog box has a display limit of 37
         characters.
   

3. When you run this query, you will be prompted with the message you specified in the LIKE statement. To find records where the LastName starts with a "D", type "D" (without the quotation marks) and begin the search. To find records where the LastName has a "D" anywhere in the field, type "D" (without the quotation marks) and begin the search.

REFERENCES