S
Stephen Rasey
(this is a cross-posting from microsoft.public.excel.programming with some
editing)
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. I am doing that,
but my dao recordset.FindFirst gives a NoMatch. Am I blundering?
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 using a FindFirst method. 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. I double the single
quote. I do not get a SQL error. But it will not find the record.
Here is an example. The 14th character in the hash stored 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 (as seen in the
Immediate window)
~8æQzzûù¦ ÿ5'?;
I have a small function to replace a single quote with two single quotes as
I build the criteria SQL string.
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
When I attempt to write the Hash as a new record, I error because the Access
Index already has the Hash of that value.
Maybe the non-printing characters are part of the problem. Other Hashes
with non-printing characters work if they do not have single quotes.
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.
I am on the lookout for a Hash function that provided a more restricted set
of characters. the wwGetMD5Hash2 function will return asc(0), asc(10),
asc(39) and some other characters that might be problematic in Excel and
Access.
Stephen Rasey
WiserWays, LLC
Houston
http://excelsig.org
editing)
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. I am doing that,
but my dao recordset.FindFirst gives a NoMatch. Am I blundering?
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 using a FindFirst method. 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. I double the single
quote. I do not get a SQL error. But it will not find the record.
Here is an example. The 14th character in the hash stored 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 (as seen in the
Immediate window)
~8æQzzûù¦ ÿ5'?;
I have a small function to replace a single quote with two single quotes as
I build the criteria SQL string.
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
When I attempt to write the Hash as a new record, I error because the Access
Index already has the Hash of that value.
Maybe the non-printing characters are part of the problem. Other Hashes
with non-printing characters work if they do not have single quotes.
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.
I am on the lookout for a Hash function that provided a more restricted set
of characters. the wwGetMD5Hash2 function will return asc(0), asc(10),
asc(39) and some other characters that might be problematic in Excel and
Access.
Stephen Rasey
WiserWays, LLC
Houston
http://excelsig.org