Access Cookbook (2024)

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

Access normally performscase-insensitive string comparisons. You can use theOption Compare Binary statement in the declarations section of amodule to force VBA to make string comparisons that arecase-sensitive within the bounds of that module, but this affectsonly string comparisons made in a VBA module, not comparisons made bythe Jet engine. Thus, even when you run the query from a VBAOption Compare Binary procedure, any comparisons made in thequery are case-insensitive. The problem is that the Jet enginedoesn’t know how to make case-sensitive string comparisonsusing any of the standard query operators. Fortunately, you cancreate your own case-sensitive string-comparison function in anOption Compare Binary module and call this function from thequery. This solution shows you how to create the VBA function and howto use it to perform case-sensitive searches.

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

Now, open the tblWords table in 01-13.MDB (seeFigure 1-42). Notice that the word“swordfish” appears in four records, each spelled using adifferent combination of upper- and lowercase letters. Run theqryWordsCI parameter query and enter SwordFish at the prompt. Whenthe query executes, it returns all four swordfish records, not thespecific version you typed at the prompt. Now run the qryWordsCSquery, entering the same string at the prompt. This time the queryreturns only one swordfish record, the one that’s spelledexactly as you typed it.

This solution uses a simple VBA function to perform a stringcomparison. Because this function resides in a module that containsthe Option Compare Binary statement, any string comparisons madeusing procedures in this module are case-sensitive. TheacbExactMatch function is simple:

Option Compare BinaryPublic Function acbExactMatch(var1 As Variant, var2 As Variant) As Boolean If var1 = var2 Then acbExactMatch = True Else acbExactMatch = False End IfEnd Function

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

Anotheralternative, which provides slightly less flexibility, is to use theVBA StrComp function. This function can comparetwo strings on a binary basis (that is, it compares each character inthe strings, taking case into account) and returns 0 if the twostrings are exact matches. The syntax for callingStrComp 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 orwhen using one of the Find methods on an existing recordset. Forexample, the sample FindString procedure, whichcan be found in basExactFindSample, finds records usingcase-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 WithEnd Sub

When you run this procedure from the debug window using:

Call FindString("SwordFish")

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

This same technique will work with ADOrecordsets, but you can’t use StrComp oracbExactMatch with a recordset’s Seekmethod because seeks can be performed only using the built-in Accessoperators.