Quote marks and Insert Into statment

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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, " ' ", " ' ' ") & " ' ) "
 
Yes,
When I went back and checked it is the apostrophe that's causing the problem.
Thanks

Douglas J. Steele said:
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!)


rg said:
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
Click to expand...
Click to expand...
 
Back
Top