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?
Access normally performs
case-insensitive string comparisons. You can use the
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
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
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:
Import the basExactMatch module from
01-13.MDBinto your database.
Create a query for which you wish to perform a case-sensitive search. Add all the desired fields in the query grid.
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
acbExactMatch([Word], [Enter word])
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.
When you execute the query, it will return only exact, case-sensitive matches. If you run qryWordCS in the
01-13.MDBdatabase and enter “SwordFish” at the parameter prompt, you should get the datasheet shown in Figure 1-41.
Now, open the tblWords table in
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.
This solution uses a simple VBA function to perform a string
comparison. Because this function resides in a module that contains
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:
this subroutine will print the record with the specified spelling to the debug window.