Microsoft KB Archive/304390

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

Article ID: 304390

Article Last Modified on 10/20/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



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 &quot;A&quot; and &quot;C&quot;, do the following:  Create a new query in Design view. In the Show Table dialog box, click the Customers table. Click the Add button. Click Close to close the Show Table dialog box. Add the following fields to the query design grid: CustomerID, CompanyName. In the Criteria row cell for CustomerID, type the following:

Like &quot;[A-C]*&quot;

</li> Save and then run the query.

Note that the query returns the records ALFKI through CONSH.</li></ol>

Example 2

If you want a query to return the records from the Customers table where the CustomerID field starts with letters between &quot;A&quot; and &quot;CE&quot;, do the following: <ol> Create a new query in Design view.</li> In the Show Table dialog box, click the Customers table.</li> Click the Add button.</li> Click Close to close the Show Table dialog box.</li> Add the following fields to the query design grid: CustomerID, CompanyName.</li> In the Criteria row cell for CustomerID, type the following:

Like &quot;[A-B]*&quot;

</li> In the Or row cell for CustomerID, type the following:

Like &quot;C[a-e]*&quot;

</li> Save and then run the query.

Note that the query returns the records ALFKI through CENTC.</li></ol>

Example 3

If you want a query to return the records from the Customers table where the CustomerID field starts with letters between &quot;Le&quot; and &quot;O&quot;, do the following: <ol> Create a new query in Design view.</li> In the Show Table dialog box, click the Customers table.</li> Click the Add button.</li> Click Close to close the Show Table dialog box.</li> Add the following fields to the query design grid: CustomerID, CompanyName.</li> In the Criteria row cell for CustomerID, type the following:

Like &quot;L[e-z]*&quot;

</li> In the Or row cell for CustomerID, type the following:

Like &quot;[M-O]*&quot;

</li> <li>Save and then run the query.

Note that the query returns the records LEHMS through OTTIK.</li></ol>

Example 4

If you want a query to return the records from the Customers table where the CustomerID field starts with letters between &quot;Fr&quot; and &quot;Li&quot;, do the following: <ol> <li>Create a new query in Design view.</li> <li>In the Show Table dialog box, click the Customers table.</li> <li>Click the Add button.</li> <li>Click Close to close the Show Table dialog box.</li> <li>Add the following fields to the query design grid: CustomerID, CompanyName.</li> <li>In the Criteria row cell for CustomerID, type the following:

Like &quot;F[r-z]*&quot;

</li> <li>In the first Or row cell for CustomerID, type the following:

Like &quot;[G-I]*&quot;

</li> <li>In the second Or row cell for CustomerID, type the following:

Like &quot;L[a-i]*&quot;

</li> <li>Save and then run the query.

Note that the query returns the records FRANK through LINOD.</li></ol>

<div class="references_section">