DAO Findfirst

Question

I have been searching different forums to get an answer for this. I am using the findfirst method of the DAO recordset to find a record in a table. The problem I have is some of the records have values containing single quote and/or double quote. Modifying the search string using Replace function and chr(34) or chr(39) doesn't solve the problem since Findfirst returns no match. If I used '' instead of ' in a query, it runs fine and loads the corresponding record. Is this a limitation of the findfirst method?

What I've been trying is, for example

strSearchString = "Find"Me'here" (where Find"Me'here is the string that I am searching for)

rs.FindFirst "[MyField]='" & Replace(strSearchString,"'","''") & "'"

And rs.NoMatch returns True. I also tried using chr(39) instead of using the actual single quote string in the search string.

Answer 1

PS:  I perhaps should have explained how you'd apply the above when building a search criterion in code.  While the expressions as above would be fine in SQL, in code, because the expressions contain double quotes characters which represent literal quotes characters you have to double up on them again when building a string expression for the criterion.  Using your example again, if we assume the following simple table:

MyID    MyField

1          Find'Me"here

2          Find"Me'here

a simple little function to find the row with the Find"Me'here value, assuming that the single and double quotes are treated as separate characters would be like this:

Function FindFirstTest()

    Dim rst As DAO.Recordset

    Dim strSearchString As String

    Dim strCriteria As String

    strSearchString = "Find""Me'here"

    strCriteria = "Replace(MyField,"""""""",""~"") = """ & Replace(strSearchString, """", "~") & """"

    Set rst = CurrentDb.OpenRecordset("SELECT * FROM MyTable")

    With rst

        .FindFirst strCriteria

        Debug.Print .Fields("MyID")

    End With

End Function

This would return 2 in the 'immediate' window.

If we change the code to treat single and double quotes as de facto the same character:

Function FindFirstTest()

    Dim rst As DAO.Recordset

    Dim strSearchString As String

    Dim strCriteria As String

    strSearchString = "Find""Me'here"

    strCriteria = "Replace(MyField,"""""""",""'"") = """ & Replace(strSearchString, """", "'") & """"

    Set rst = CurrentDb.OpenRecordset("SELECT * FROM MyTable")

    With rst

        .FindFirst strCriteria

        Debug.Print .Fields("MyID")

    End With

End Function

It now returns 1.

Answer 2

PS:  I perhaps should have explained how you'd apply the above when building a search criterion in code.  While the expressions as above would be fine in SQL, in code, because the expressions contain double quotes characters which represent literal quotes characters you have to double up on them again when building a string expression for the criterion.  Using your example again, if we assume the following simple table:

MyID    MyField

1          Find'Me"here

2          Find"Me'here

a simple little function to find the row with the Find"Me'here value, assuming that the single and double quotes are treated as separate characters would be like this:

Function FindFirstTest()

    Dim rst As DAO.Recordset

    Dim strSearchString As String

    Dim strCriteria As String

    

    strSearchString = "Find""Me'here"

    strCriteria = "Replace(MyField,"""""""",""~"") = """ & Replace(strSearchString, """", "~") & """"

    

    Set rst = CurrentDb.OpenRecordset("SELECT * FROM MyTable")

    

    With rst

        .FindFirst strCriteria

        Debug.Print .Fields("MyID")

    End With

    

End Function

This would return 2 in the 'immediate' window.

If we change the code to treat single and double quotes as de facto the same character:

Function FindFirstTest()

    Dim rst As DAO.Recordset

    Dim strSearchString As String

    Dim strCriteria As String

    

    strSearchString = "Find""Me'here"

    strCriteria = "Replace(MyField,"""""""",""'"") = """ & Replace(strSearchString, """", "'") & """"

    

    Set rst = CurrentDb.OpenRecordset("SELECT * FROM MyTable")

    

    With rst

        .FindFirst strCriteria

        Debug.Print .Fields("MyID")

    End With

    

End Function

It now returns 1.

Brilliant! Thanks. Never thought about the possibility of replacing the field name in the search criteria with a function. Just what I was looking for.

Answer 3

I'm not clear whether you wish to equate single and double quotes characters.  If you do not, but wish to teat them as different characters, you can call the Replace function to replace the literal double quotes character with a high ASCII character such as the tilde.  

A literal double quotes character is represented in a string expression by a pair of contiguous double quotes characters, so if, using your example, we first assign identical values to two variables:

strSearchString = "Find""Me'here"

MyField = "Find""Me'here"

? strSearchString,MyField

Find"Me'here  Find"Me'here

We can then compare them as follows:

? Replace(MyField,"""","~") = Replace(strSearchString,"""","~")

True

If we then transpose the single and double quotes characters in the MyField variable:

MyField = "Find'Me""here"

? strSearchString,MyField

Find"Me'here  Find'Me"here

Comparing the values of the variables in the same way now returns a mismatch:

? Replace(MyField,"""","~") = Replace(strSearchString,"""","~")

False

If, on the other hand, you wish to equate single and double quotes characters as the same de facto character, replace the double quotes characters with single quotes characters:

strSearchString = "Find""Me'here"

MyField = "Find""Me'here"

? Replace(MyField,"""","'") = Replace(strSearchString,"""","'")

True

MyField = "Find'Me""here"

? Replace(MyField,"""","'") = Replace(strSearchString,"""","'")

True

Now both string expressions match regardless of the transposition of the single and double quotes characters.