Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 1.14 Create a Query That Uses Case-Sensitive Criteria

1.14.1 Problem

You
have a table of words, some of which appear multiple times. Each
instance of these words is spelled using a different combination of
upper- and lowercase. You'd like to create a query
that finds exact matches using case-sensitive criteria, but no matter
what you type into the criteria for the query, Access always returns
all instances of the same word, disregarding each
instance's case. Is there any way to create a query
that can select records based on case-sensitive criteria?

1.14.2 Solution

Access normally performs
case-insensitive string comparisons. You can use the
Option Compare
Binary statement in the declarations section of a
module to force VBA to make string comparisons that are
case-sensitive within the bounds of that module, but this affects
only string comparisons made in a VBA module, not comparisons made by
the Jet engine. Thus, even when you run the query from a VBA
Option Compare Binary
procedure, any comparisons made in the query are case-insensitive.
The problem is that the Jet engine doesn't know how
to make case-sensitive string comparisons using any of the standard
query operators. Fortunately, you can create your own case-sensitive
string-comparison function in an Option
Compare Binary module and call
this function from the query. This solution shows you how to create
the VBA function and how to use it to perform case-sensitive
searches.

To use this technique in your own database, follow these steps:

1. Import the basExactMatch module from 01-14.MDB
   into your database.

2. Create a query for which you wish to perform a case-sensitive search.
   Add all the desired fields in the query grid.

3. Create a computed field in the query grid that references the
   

   acbExactMatch function found in basExactMatch.
   For example, if you wish to compare the Word field with a
   user-entered parameter, create a field like that shown in Table 1-7.

   You can also use a hard-coded string instead of a parameter. We used
   a parameter in the qryWordCS query, shown in design view in Figure 1-40.

Figure 1-40. qryWordCS uses acbExactMatch to filter records using case-sensitive criteria

4. When you execute the query, it will return only exact, case-sensitive
   matches. If you run qryWordCS in the

   01-14.MDB
   database and enter "SwordFish" at
   the parameter prompt, you should get the datasheet shown in Figure 1-41.

Figure 1-41. qryWordCS is case-sensitive, so it returns only one matching record

Now, open the tblWords table in

01-14.MDB (see
Figure 1-42). Notice that the word
"swordfish" appears in four
records, each spelled using a different combination of upper- and
lowercase letters. Run the qryWordsCI parameter query and enter
SwordFish at the prompt. When the query executes, it returns all four
swordfish records, not the specific version you typed at the prompt.
Now run the qryWordsCS query, entering the same string at the prompt.
This time the query returns only one swordfish record, the one
that's spelled exactly as you typed it.

Figure 1-42. tblWords contains four swordfish records with different capitalizations

1.14.3 Discussion

This solution uses a simple VBA function to perform a string
comparison. Because this function resides in a module that contains
the Option Compare
Binary statement, any string comparisons made
using procedures in this module are case-sensitive. The

acbExactMatch function is simple:

Option Compare Binary
Public Function acbExactMatch(var1 As Variant, var2 As Variant) As Boolean
acbExactMatch = (var1 = var2)
End Function

This function returns True only when the strings
are spelled exactly the same way. The code compares the values in
var1 and var2, and returns True if the values are
equal, and False if they're not.

Another
alternative, which provides slightly less flexibility, is to use the
VBA

StrComp function. This function can compare
two strings on a binary basis (that is, it compares each character in
the strings, taking case into account) and returns 0 if the two
strings are exact matches. The syntax for calling

StrComp in qryWordsCS looks like this:

StrComp([Word], [Enter Word], 0)

and the Criteria is 0 (not -1, as shown earlier).