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:

Table 1-7. Settings for the acbExactMatch field

Attribute	Value
Field	acbExactMatch([Word], [Enter word])
Table	(Blank)
Sort	(Blank)
Show	(Unchecked)
Criteria	-1

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

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