ACC: How to Return Case-Sensitive Matches in Queries

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

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

You can use the Asc() function to have Microsoft Access differentiate between case sensitive text strings. When you use this function in a select query, Microsoft Access can locate an exact case sensitive match.

MORE INFORMATION

This technique is useful in a situation where you need to find all the records in a field that contain lowercase text rather than uppercase text for the first character. An example of this might be in the Employees table of the Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0) sample database.

NOTE: You can perform full case-sensitive comparisons in a query using the built-in StrComp() function. All you need to do is create a column with the following expression

   StrComp([FieldName],"SearchForThis",0).

Please note that the following example will find only the first character.

Suppose the text field called FirstName contains values such as the following records:

   FirstName
   ---------
   andrew
   Andrew
   Nancy

   NOTE: In versions 1.x and 2.0, there is a space in the First Name field.

   Query: QueryTest
   --------------------------------
   Field name: FirstName
      Show: True
   Field name: Asc([Firstname])
      Show: True
      First Criteria Line:  Asc("a")

REFERENCES

For more information about the Asc() function, search the Help Index for "Asc function."

For more information about Case-Sensitive comparisons, search the Help Index for "case sensitivity."