Microsoft KB Archive/95605

= ACC: How to Return Case-Sensitive Matches in Queries =

Article ID: 95605

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 Q95605



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 sample database (or NWIND.MDB in versions 1.x and 2.0).

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

  StrComp([FieldName],&quot;SearchForThis&quot;,0).

and then set the criteria of this column to 0.

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

If you need to find all the records that contain the lower case &quot;andrew&quot; instead of &quot;Andrew,&quot; use the Asc function. To do this, create the following select query:

  Query: QueryTest Field name: FirstName

Show: True

Field name: Asc([Firstname])

Show: True First Criteria Line: Asc(&quot;a&quot;)

Note that when you run this query, the query returns only the record for &quot;andrew,&quot; not for &quot;Andrew.&quot;

