DoCMD.RunSQL Update and Apostrophe

[Bruce M. Thompson]

DoCmd.RunSQL ("UPDATE PROP SET n_rue = '" & Replace(rs1("rue"), "'", "''") &

"', n_rueno = " & rs1("rueno") & " WHERE RUE = '" & Replace(rs1("rue"), "'",
"''") & "' AND RUENO = " & rs1("rueno"))

The problem arives if the street (ie. "rue") called is , for example, "de
l' Astrolab". I have tried various functions (including the built in
'replace' function, used above) , but none seems to work. Acording to the
error message, the problem is eminating from the "WHERE" part of the SQL
statement (the error message refers to a "Syntax" problem, error number
3144, etc).

It appears that you could simply replace each embedded single quote (') with a
double double quote (""), so:

("UPDATE PROP SET n_rue = '" & Replace(rs1("rue"), "'",

.... would become ...

("UPDATE PROP SET n_rue = """ & Replace(rs1("rue"), """",

.... and so on.

 

[John S]

Strange problem: I am getting an error message in an update command . The
command is evoked if data from two open recordsets corresponds. The actual
line is

DoCmd.RunSQL ("UPDATE PROP SET n_rue = '" & Replace(rs1("rue"), "'", "''") &
"', n_rueno = " & rs1("rueno") & " WHERE RUE = '" & Replace(rs1("rue"), "'",
"''") & "' AND RUENO = " & rs1("rueno"))

The problem arives if the street (ie. "rue") called is , for example, "de
l' Astrolab". I have tried various functions (including the built in
'replace' function, used above) , but none seems to work. Acording to the
error message, the problem is eminating from the "WHERE" part of the SQL
statement (the error message refers to a "Syntax" problem, error number
3144, etc).

Anyone had the same problem? Anyone have a solution?.

John S
Aylmer, PQ

 

[Joe Fallon]

Try using a function like this to handle quotes.

Example:
HandleQuotes("John ""Big-Boy"" O'Neil")
returns
"John " & Chr$(34) & "Big-Boy" & Chr$(34) & " O'Neil"

Public Function HandleQuotes(strValue As String, Optional strDelimiter As
String = """") As String
On Error GoTo Err_HandleQuotes

' In:
' strValue: Value to fix up.
' strDelimiter: (Optional) Delimiter to use.
' Out:
' Return value: the text, with delimiters fixed up.
' Example:
' HandleQuotes("John ""Big-Boy"" O'Neil") returns
' "John " & Chr$(34) & "Big-Boy" & Chr$(34) & " O'Neil"

Dim strInsert As String
strInsert = "Chr$(" & Asc(strDelimiter) & ")"
HandleQuotes = strDelimiter & Replace(strValue, strDelimiter, strDelimiter
& " & " & strInsert & " & " & strDelimiter) & strDelimiter

Exit_HandleQuotes:
Exit Function

Err_HandleQuotes:
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source &
vbCrLf & Err.Description, , "SystemCode - HandleQuotes"
Resume Exit_HandleQuotes

End Function

 

[Bruce M. Thompson]

Thanks for the advice. I did try the following, but it doesn"t seem to work:

DoCmd.RunSQL ("UPDATE PROP SET n_rue = """ & Replace(rs("orue"),
"'", """") & """ , n_rueno = " & rs("onum") & " WHERE RUE = """ & rs("orue")
& """ AND RUENO = " & rs("onum"))

Am I missing something?

Well, only because I forgot to tell you to lose the "Replace()" function
implementation. Using the double double quotes alleviates the need to do
anything with the single quotes. So, replace ...

Replace(rs("orue"), "'", """")

.... with ...

rs("orue")

.... and likewise with the other occurrences of "Replace()".

Sorry about that. :-{

 

[John S]

Ah, an abject lesson in jumping to conclusionss: my problem was that there
were pesky nulls in one of the two databases being compared. So once I
filtered out those pesky nulls, and did apostrophy replacements in my
"where" statement, all worked fine.

Thank you again for your excellent advice.