rs.FindFirst searching with single/double quotes

[colinsw]

We have a requirement to search the 'CODE' field within an Access database where the 'CODE' field may contain one or more instances of either single or double quotes. The data that is put in this field comes from an external source and we are unable to change its format. The data is also used when referring back to its source, so we cannot remove the single/double quotes on import.

My basic question is how do I make this work when using a Dynaset? We know that we can use either SQL or a snapshot, both of which work, however for various reasons neither of those methods is an option.

Below is some sample code that demonstrates the problem:

The table within the database contains two fields 'CODE' and 'NAME' where code is the primary key. Both fields are alphanumeric. Add a record to the table where 'CODE' = "o'sul" (no double quotes) and 'NAME' = "o'sullivan".

Set rs = db.OpenRecordset("Business Partners",_ dbOpenDynaset)
rs.FindFirst "Code = 'o''sul'"
If rs.NoMatch Then
Debug.Print "Dynaset Not Found"
Else
Debug.Print "Dynaset Found"
End If

Note that this will always return 'Dynaset Not Found' for the data example given above, whereas:

Set rs = db.OpenRecordset("SELECT * FROM [Business Partners] WHERE Code='o''sul'", dbOpenDynaset)
If Not rs.BOF And Not rs.EOF Then
Debug.Print "SQL Found"
End If

AND

Set rs = db.OpenRecordset("Business Partners",_ dbOpenSnapshot)
rs.FindFirst "Code = 'o''sul'"
If rs.NoMatch Then
Debug.Print "Snapshot Not Found"
Else
Debug.Print "Snapshot Found"
End If


always return "SQL found" or "Snapshot Found".

 

[sunaj]

Hi,

I've got exactly the same problem and found this:

http://support.microsoft.com/support/kb/articles/Q202/9/55.ASP

Double quotes should be no problem, but if you wan't to search for multiple single quotes, you'll have to use the MutliFind method or do it directly in the database:
con.Execute "SELECT ... FROM ... WHERE ...", this works with any combination of quotes but is not very fast or elegant....

If you come up with something better, I'm very interested...

Sunaj

 

[colinsw]

We found the solution to this problem and I've returned to let anyone else interested know how we did it. Using one of these will do it:

rs.FindFirst "Code = '" & "o'sul" & "'"

or

rs.FindFirst "Code = """ & "o'sul" & """

You can also try substituting the " with a Chr(34) (double quote):

rs.FindFirst "Code = " & chr(34) & "o'sul" & chr(34)

 

[sunaj]

I'm a bit confused here...

It is possible to use the .find or .findfirst method looking for strings that only contains ONE single quote and the standard way to deal with this is:
rs.FindFirst "Code='" & replace("o'sul","'","''&quot & "'"
i.e. use the replace function to double single quotes.

Problem only arises when the string you are looking for contains MULTIPLE single quotes. As far as I can see your suggestion does not solve this problem (i.e. you method will fail if the string you are looking for contains more than one single quote).

Or have I misunderstood your solution?
Sunaj