ACC: How to Return Case-Sensitive Matches in Queries

ID: Q95605

The information in this article applies to:

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 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],"SearchForThis",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 "andrew" instead of "Andrew," 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("a")

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

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

Additional query words: chr chr$ how to

Keywords : kbusage QryParm
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbhowto