ACC: How to Return Case-Sensitive Matches in Queries (95605)

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