Access Cookbook

Create a Query That Uses Case-Sensitive Criteria

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?

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

   Table 1-7. Settings for the acbExactMatch field

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

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

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
    If var1 = var2 Then
        acbExactMatch = True
    Else
        acbExactMatch = False
    End If
End Function

This function returns True only when the strings are spelled exactly the same way.

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

You can also use this function when creating a recordset in VBA or when using one of the Find methods on an existing recordset. For example, the sample FindString procedure, which can be found in basExactFindSample, finds records using case-sensitive criteria:

Public Sub FindString(strWord As String)

    ' Demonstrates how to find a record using case-sensitive criteria.
    ' Call this function like so:
    '     Call FindString("SwordFish")

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim varCriteria As Variant
    
    Const acbcQuote = """"
    
    Set db = CurrentDb(  )
    
    ' Create recordset on tblWords.
    Set rst = db.OpenRecordset("tblWords", dbOpenDynaset)
    
    ' Build up the criteria to be used with the Find methods
    ' strWord was passed in as a parameter.
    varCriteria = "(acbExactMatch([Word]," & acbcQuote & _
     strWord & acbcQuote & ")<>0)"

    ' If you want to use StrComp(  ) and not carry around the extra module
    ' containing acbExactMatch, use the following expression:
    ' varCriteria = "StrComp([Word]," & acbcQuote & _
    '  strWord & acbcQuote & ", 0) = 0"    

    Debug.Print "ID", "Word"
    
    With rst
        ' Do an initial find.
        .FindFirst varCriteria
        
        ' Print values from record to debug window. Continue to find
        ' additional matches until there are no more.
            Do While Not .NoMatch
                Debug.Print ![ID], ![Word]
         
            .FindNext varCriteria
        Loop
        .Close
    End With

End Sub

When you run this procedure from the debug window using:

Call FindString("SwordFish")

this subroutine will print the record with the specified spelling to the debug window.

This same technique will work with ADO recordsets, but you can’t use StrComp or acbExactMatch with a recordset’s Seek method because seeks can be performed only using the built-in Access operators.