ACC: Case Sensitivity in Queries Differs with Linked Tables

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

SUMMARY

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

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.

1. Open the sample database Northwind.mdb.

2. Export the Customers table to a dBase V file named Newcust.dbf.

3. Create a new Microsoft Access database.

4. Create a link (or attachment in Microsoft Access 2.0 or earlier) to the dBASE V file Newcust.dbf.

5. Create a new query based on the linked Newcust table. Name this query QueryTest.

6. Drag all the Newcust table fields to the query grid.

7. Make the following change to the CITY column:

   Query: QueryTest --------------------------------- FieldName: CITY

             Show: True
             First Criteria Line: Like "s*"
   
       Note that the "s" is lowercase.
   
   

8. Run the query. Note that the result is an empty recordset.

9. Import the same dBASE table.

10. 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.

11. Add the same criteria to the CITY column as you used in Step 4.

12. Run the query. You now receive all the records where the name of the city begins with the letter "S" or "s."

REFERENCES

For more information about criteria in queries, search the Help Index for "Criteria," or ask the Microsoft Access 97 Office Assistant.

For more information about UCase() function, search the Help Index for "UCase, UCase$ Function."