Microsoft KB Archive/100921

= ACC: Case Sensitivity in Queries Differs with Linked Tables =

Article ID: 100921

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q100921



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



SUMMARY
When you query on a linked (attached) table, the criteria becomes case sensitive if the linked table is in a format other than a Microsoft Access table, such as dBASE, Paradox, ODBC, and so on.



MORE INFORMATION
There are several ways to prevent case sensitivity in criteria when you query on linked tables:

 Import the linked table, and then base your query on the imported table. Microsoft Access criteria values are not case sensitive. Include a native Microsoft Access table joined to the linked table in the query. Criteria values are not case sensitive.  Use the UCase function in your query to ensure that the field in the query recordset is returned in uppercase, and specify the criteria in uppercase. The following sample query demonstrates how to use the UCase function in a query:

    Query: Query1 ---    FieldName: UCase(CITY) Show: True First Criteria Line: Like "S*" (Note that the "S" is uppercase)  Be aware that you are using a linked table and be sure that the values you use as criteria match the case of the values in the linked table.

Steps to Reproduce Behavior
NOTE: If you are using Microsoft Access 2.0 or earlier you can skip steps 1 and 2. The Newcust.dbf sample dBASE file is supplied with Microsoft Access 1.x and 2.0.

 Open the sample database Northwind.mdb.</li> Export the Customers table to a dBase V file named Newcust.dbf.</li> Create a new Microsoft Access database.</li> Create a link (or attachment in Microsoft Access 2.0 or earlier) to the dBASE V file Newcust.dbf.</li> Create a new query based on the linked Newcust table. Name this query QueryTest.</li> Drag all the Newcust table fields to the query grid.</li>  Make the following change to the CITY column:

<pre class="fixed_text">      Query: QueryTest -      FieldName: CITY Show: True First Criteria Line: Like "s*"

Note that the "s" is lowercase. </li> Run the query. Note that the result is an empty recordset.</li> Import the same dBASE table.</li> Create a new query using the newly imported table and, after dragging all of the fields to the query grid, save the query as QueryTest2.</li> Add the same criteria to the CITY column as you used in Step 4.</li> Run the query. You now receive all the records where the name of the city begins with the letter "S" or "s."</li></ol>

<div class="references_section">