Quote marks and Insert Into statment

[Guest]

I build an Insert Into statment using a variable, and it is working great,
except when the value I'm putting into a field contains a quote mark.

Basically the code is strSQL = "Insert into [table_name]([field_name) " & _
"Values('" & string_variable & "')'

When Access runs into a quote in the string_variable it sees it as a the end
of my string. How can I use quotes in the variable?
-rg

 

[Douglas J. Steele]

Assuming that by "quote mark", you really mean apostrophe, try:

strSQL = "Insert into [table_name]([field_name) " & _
"Values('" & Replace(string_variable, "'", "''") & "')"

Exagerated for clarity, that's

strSQL = "Insert into [table_name]([field_name) " & _
"Values(' " & Replace(string_variable, " ' ", " ' ' ") & " ' ) "

 

[Guest]

Yes,
When I went back and checked it is the apostrophe that's causing the problem.
Thanks

Assuming that by "quote mark", you really mean apostrophe, try:

strSQL = "Insert into [table_name]([field_name) " & _
"Values('" & Replace(string_variable, "'", "''") & "')"

Exagerated for clarity, that's

strSQL = "Insert into [table_name]([field_name) " & _
"Values(' " & Replace(string_variable, " ' ", " ' ' ") & " ' ) "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I build an Insert Into statment using a variable, and it is working great,
except when the value I'm putting into a field contains a quote mark.

Basically the code is strSQL = "Insert into [table_name]([field_name) " &
_
"Values('" & string_variable & "')'

When Access runs into a quote in the string_variable it sees it as a the
end
of my string. How can I use quotes in the variable?
-rg