FindFirst with an apostrophe and/or a double quote

[Tom]

Hello

I'm sure that most of the Access programmers know that problem. When a
string contains an apostrophe or a double quote, the FindFirst-function
fails. At the moment, I'm using the following command:

rs.FindFirst "Destination='" & Replace("Côte d'Azur", "'", "''") & "'"

This code works fine, but if the string search contains an apostrophe, the
FindFirst function fails. Well I can change the code as follows

rs.FindFirst "Destination=""" & Replace("Côte d'Azur", """", """""") & """"
(double quotes instead of apostrophes)

but now I have the same problem if the search-string contains a double
quote. How to solve this? EVERY COMBINATION should be found with the
FindFirst-function? Any help would be appreciated.

Tom

 

[Van T. Dinh]

Have you tried the second one (with double-quotes)?

AFAIK, it should work work correctly as you have replaced the included
double-quote with 2 double-quotes.

Actually, I think the first one should work correctly also if single
quote(s) are included in the search String.

 

[Marshall Barton]

I'm sure that most of the Access programmers know that problem. When a
string contains an apostrophe or a double quote, the FindFirst-function
fails. At the moment, I'm using the following command:

rs.FindFirst "Destination='" & Replace("Côte d'Azur", "'", "''") & "'"

This code works fine, but if the string search contains an apostrophe, the
FindFirst function fails. Well I can change the code as follows

rs.FindFirst "Destination=""" & Replace("Côte d'Azur", """", """""") & """"
(double quotes instead of apostrophes)

but now I have the same problem if the search-string contains a double
quote. How to solve this? EVERY COMBINATION should be found with the
FindFirst-function?

I don't see the problem. The general rule is to double up
whichever quote is used to enclose the string. In the first
case the find argument should end up as:
Destination='Côte d''Azur'
and in the second case:
Destination="Côte d'Azur"
both of which are perfectly legal and will do what you want.

In a more complicated case where you want to search for the
string: Tom's title is "Chief"
Either of your approaches will work. The first will search
for:
Destination='Tom''s title is "Chief"'
and the second is:
Destination="Tom's title is ""Chief"""

Maybe you are getting confused by using a literal string in
the tests? If so, use a string variable instead:
strSearch = "Tom's title is ""Chief"""
then use either:
rs.FindFirst "Destination='" & Replace(strSearch, "'",
"''") & "'"
or
rs.FindFirst "Destination=""" & Replace(strSearch, """",
"""""") & """"

 

[Tom]

Ok, another example:

Assume I want to seatrch the following string:

Côte d'Azur, "Palace" Hotel

This string contains an apostrophe AND a double quote. How to do that?

Thomas

 

[Douglas J Steele]

How is that any different that the example Marsh gave of

Tom's title is "Chief"

BTW, for more on this topic, check my May, 2004 "Access Answers" column in
Pinnacle Publication's "Smart Access". You can download the column (and
sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, another example:

Assume I want to seatrch the following string:

Côte d'Azur, "Palace" Hotel

This string contains an apostrophe AND a double quote. How to do that?

Thomas

 

[Tom]

Ok, thanks for your help. Maybe your example works with the
Filter-statement, but it seems to fail with the FindFirst-function. I
created the following example:

Sub Test()
Dim rs As DAO.Recordset
Dim s(1 To 3) As String
Dim i As Integer

Set rs = CurrentDb.OpenRecordset("T_Destinationen", dbOpenDynaset)

s(1) = "Côte d'Azur"
s(2) = """Palace"" Hotel"
s(3) = "Côte d'Azur, ""Palace"" Hotel"

For i = 1 To 3
rs.FindFirst "Destination='" & Replace(s(i), "'", "''") & "'"
Debug.Print "Found " & i & ": " & Not rs.NoMatch
Next i
End Sub

This procedure returns the following results:

Found 1: False
Found 2: True
Found 3: False


At next I'll make a test with a WHERE-query...

Tom

How is that any different that the example Marsh gave of

Tom's title is "Chief"

BTW, for more on this topic, check my May, 2004 "Access Answers" column in
Pinnacle Publication's "Smart Access". You can download the column (and
sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Tom]

Very interesting... The following example works!!!

Sub Test()
Dim i As Integer
Dim s(1 To 3) As String
Dim rs As DAO.Recordset

s(1) = "Côte d'Azur"
s(2) = """Palace"" Hotel"
s(3) = "Côte d'Azur, ""Palace"" Hotel"

For i = 1 To 3
Set rs = CurrentDb.OpenRecordset("SELECT * FROM T_Destinationen WHERE
Destination = '" & Replace(s(i), "'", "''") & "'", dbOpenDynaset)
Debug.Print "Found " & i & ": " & CStr(rs.RecordCount > 0)
Next i
End Sub

This seems to be a serious bug in the FindFirst function... :-(

How is that any different that the example Marsh gave of

Tom's title is "Chief"

BTW, for more on this topic, check my May, 2004 "Access Answers" column in
Pinnacle Publication's "Smart Access". You can download the column (and
sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Marshall Barton]

I don't know what to tell you. I have not tun into a
problem with this, although I admit that I usually use
double quote as the delimiter (as in your second choice).

 

[Tom]

Marshall,

Ok I changed the programm as follows:

Sub Test_2()
Dim rs As DAO.Recordset
Dim s(1 To 3) As String
Dim i As Integer

Set rs = CurrentDb.OpenRecordset("T_Destinationen", dbOpenDynaset)

s(1) = "Côte d'Azur"
s(2) = """Palace"" Hotel"
s(3) = "Côte d'Azur, ""Palace"" Hotel"

For i = 1 To 3
rs.FindFirst "Destination=""" & Replace(s(i), """", """""") & """"
Debug.Print "Found " & i & ": " & Not rs.NoMatch
Next i
End Sub

Now I'm using double quotes instead of apostrophes. The result of the
function is the following:

Found 1: True
Found 2: False
Found 3: False

What am I doing wrong?

Tom

I don't know what to tell you. I have not tun into a
problem with this, although I admit that I usually use
double quote as the delimiter (as in your second choice).
--
Marsh
MVP [MS Access]

Ok, thanks for your help. Maybe your example works with the
Filter-statement, but it seems to fail with the FindFirst-function. I
created the following example:

Sub Test()
Dim rs As DAO.Recordset
Dim s(1 To 3) As String
Dim i As Integer

Set rs = CurrentDb.OpenRecordset("T_Destinationen", dbOpenDynaset)

s(1) = "Côte d'Azur"
s(2) = """Palace"" Hotel"
s(3) = "Côte d'Azur, ""Palace"" Hotel"

For i = 1 To 3
rs.FindFirst "Destination='" & Replace(s(i), "'", "''") & "'"
Debug.Print "Found " & i & ": " & Not rs.NoMatch
Next i
End Sub

This procedure returns the following results:

Found 1: False
Found 2: True
Found 3: False


At next I'll make a test with a WHERE-query...

Tom

 

[Marshall Barton]

I spent some time and set up a test using the same examples
you used and I got the correct results. I don't understand
why it would fail for you, but I have never even heard of
anyone running into the problem you're seeing.

Grasping at a very weak straw as a last gasp gesture, try
some more tests that do not include the accented character.

 

[atfis52]

Possible work-around and explanation of why some people don't see problem

There appears to be a work-around in that FindFirst works as expected when the argument is passed in parentheses. Thus

rs.FindFirst "Destination='" & Replace(strSearch, "'", "''") & "'"

will not find destinations containing single quotes, whereas

rs.FindFirst ("Destination='" & Replace(strSearch, "'", "''") & "'" )

will work fine.

As far as I know, all the parentheses should do is force the argument to be passed by value rather than by reference. Since the argument is already an expression I can't see this should make any difference, but it does!

 

[Brad McDowell]

atfis52: I tried it that way, but the parenthesis need to be inside the string passed.

rs.FindFirst "(Destination='" & Replace(strSearch, "'", "''") & "')"

I generally use a function for single quoting strings so mine looks like this

Public Function SQuote(ByVal sItem As String) As String
Const cTIC = "'"
SQuote = cTIC & Replace(sItem, cTIC, "''") & cTIC
End Function

rs.FindFirst "(Destination=" & SQuote( strSearch) & ")"