SQL statement with special characters

[Daniel Pineault]

I built an SQL statement the I then execute. The problem being that some
info within it contain special caharacter and thus generates errors. How can
I get around this?

sSQL ="UPDATE tbl_clients " & _
"SET [Hist ID]='" & rs![Hist ID] & _
"', [Lead ID]='" & rs![Lead ID] & "', [Hist Date]=#" & rs![Hist
Date] & _
"#, Source='" & rs![Source] & "'"

For illustrative purposes, rs![Source] equals "you're". The apostrophe seem
generate an error.

Thank you,

Daniel Pineault

 

[Daniel Pineault]

Did some digging and came across a posting from Douglas Steele with the answer

Replace(, "'", "''")

by using the Replace Function on the variables containing string values in
my SQL statement it will escape them, thus importing them just fine.

Daniel P

 

[John W. Vinson]

I built an SQL statement the I then execute. The problem being that some
info within it contain special caharacter and thus generates errors. How can
I get around this?

sSQL ="UPDATE tbl_clients " & _
"SET [Hist ID]='" & rs![Hist ID] & _
"', [Lead ID]='" & rs![Lead ID] & "', [Hist Date]=#" & rs![Hist
Date] & _
"#, Source='" & rs![Source] & "'"

For illustrative purposes, rs![Source] equals "you're". The apostrophe seem
generate an error.

It's not an error. The apostroph in you're is being seen as the closing
delimiter, paired with the apostrophe in the expresson Source = ' within your
string constant.

You've found one solution - double up the apostrophes; another is to use " as
a delimiter rather than ' in your SQL. To do so, use a double doublequote
wherever you want a single doublequote (How's THAT for doubletalk!):

sSQL ="UPDATE tbl_clients " & _
"SET [Hist ID]='" & rs![Hist ID] & _
"', [Lead ID]='" & rs![Lead ID] & "', [Hist Date]=#" & rs![Hist
Date] & _
"#, Source=""" & rs![Source] & """"


John W. Vinson [MVP]