ACC2002: How to Create a Query That Returns Records Based on a Range of Letters in a Text Field (304390)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q304390
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

This article shows you how to create queries that return records based on a range of letters in a text field. Although there are several ways to do this in Access, this article shows you how to specify the range by using brackets.

MORE INFORMATION

All the examples in this article are based on the Northwind sample database and the Customers table in that database.

Example 1

If you want a query to return records from the Customers table where the CustomerID field starts with letters between "A" and "C", do the following:
  1. Create a new query in Design view.
  2. In the Show Table dialog box, click the Customers table.
  3. Click the Add button.
  4. Click Close to close the Show Table dialog box.
  5. Add the following fields to the query design grid: CustomerID, CompanyName.
  6. In the Criteria row cell for CustomerID, type the following:

    Like "[A-C]*"

  7. Save and then run the query.

    Note that the query returns the records ALFKI through CONSH.
Example 2

If you want a query to return the records from the Customers table where the CustomerID field starts with letters between "A" and "CE", do the following:
  1. Create a new query in Design view.
  2. In the Show Table dialog box, click the Customers table.
  3. Click the Add button.
  4. Click Close to close the Show Table dialog box.
  5. Add the following fields to the query design grid: CustomerID, CompanyName.
  6. In the Criteria row cell for CustomerID, type the following:

    Like "[A-B]*"

  7. In the Or row cell for CustomerID, type the following:

    Like "C[a-e]*"

  8. Save and then run the query.

    Note that the query returns the records ALFKI through CENTC.
Example 3

If you want a query to return the records from the Customers table where the CustomerID field starts with letters between "Le" and "O", do the following:
  1. Create a new query in Design view.
  2. In the Show Table dialog box, click the Customers table.
  3. Click the Add button.
  4. Click Close to close the Show Table dialog box.
  5. Add the following fields to the query design grid: CustomerID, CompanyName.
  6. In the Criteria row cell for CustomerID, type the following:

    Like "L[e-z]*"

  7. In the Or row cell for CustomerID, type the following:

    Like "[M-O]*"

  8. Save and then run the query.

    Note that the query returns the records LEHMS through OTTIK.
Example 4

If you want a query to return the records from the Customers table where the CustomerID field starts with letters between "Fr" and "Li", do the following:
  1. Create a new query in Design view.
  2. In the Show Table dialog box, click the Customers table.
  3. Click the Add button.
  4. Click Close to close the Show Table dialog box.
  5. Add the following fields to the query design grid: CustomerID, CompanyName.
  6. In the Criteria row cell for CustomerID, type the following:

    Like "F[r-z]*"

  7. In the first Or row cell for CustomerID, type the following:

    Like "[G-I]*"

  8. In the second Or row cell for CustomerID, type the following:

    Like "L[a-i]*"

  9. Save and then run the query.

    Note that the query returns the records FRANK through LINOD.

REFERENCES

For more information about returning a range of records in a query, click Microsoft Access Help on the Help menu, type Examples of expressions in the Office Assistant or the Answer Wizard, and then click Search to view "Ranges of values (>, <, >=, <=, <>, or Between...And)."
Modification Type:MajorLast Reviewed:10/20/2003
Keywords:kbhowto KB304390
©2003 Microsoft Corporation. All rights reserved.