I know when you need to create a query string and the data contains a single
quote, you must double the quote as an escape sequence.

Maybe I am blundering, but I cannot figure out why this DAO recordset
FindFirst is returning a noMatch.

The situation. I am using a wwGetMD5Hash2 function See:
http://excelsig.org/VBA/wwHash.htm#wwGetMD5Hash2
I use this to track whole sets of assumptions in my portfolio models.
Hundreds of assumption cells combined into one 16 character Hash. That
Hash I write to a database table called H70. The HASH field is a No
Duplicates index. Each record has an IDHash, a Long integer, random
autonumber.

When I run the model, I check the Hash result against the H70 table in the
database. If the Hash cannot be found, I create a new record. If it can
be found, I retrieve the IDHash long integer value.

It works 95% of the time. But if the Hash string contains a single quote,
my DAO rsH70.FindFirst fails to find the Hash string.

Here is an example. The 14th character in the hash in a row of the H70
table contains a single quote.
Field:HASH in Table H70 contains
~8æQzzûù¦ÿ5'?;

The Hash Value in Spreadsheet read into variable strHash
~8æQzzûù¦ÿ5'?;

I have a small function to replace a single quote with two single quotes.
Dim sql as string
sql is set to the value
sql = "HASH = '" & wwQuoteFix(strHash) & "'"

Function wwQuoteFix(str1 As String) As String
wwQuoteFix = Replace(str1, "'", "''")
End Function


In the H70Check function below, the value of sql in the find first statement
is:
HASH = '~8æQzzûù¦ÿ5''?;'
so I think I have correctly doubled the single quote within the string.

yet the DAO recordset.NoMatch is true

Function H70Check(strHash As String) As Long
'return the IDHashProspSched if the strHash is found in
H70HashProspSched table
'return 0 if not found. Protect against 0 being a legitimate ID.
Dim sql As String
sql = "HASH = '" & wwQuoteFix(strHash) & "'" 'Rasey 040804 v24j
With rsH70ProspSched 'a Public DAO.Recordset
.FindFirst sql
If .NoMatch Then
H70Check = 0
Else
H70Check = !IDHashProspSched
End If
End With
End Function

Maybe the non-printing characters are part of the problem. Other Hashes
with non-printing characters but without single quotes work fine.

I am prepared to be very embarrased at some dumb mistake I made. I'll
accept that. I am out of ideas.
I hope someone sees the problem.

Stephen Rasey
WiserWays, LLC
Houston
http://excelsig.org