SQL/VBA

[auujxa2 via AccessMonster.com]

I have a strSQL that gives me syntax errors, when someone enters an
apostrophe in the text box as a value. (ie. Men's Clothing)

The apostrophe is messing everything up.. So I don't have to put a (do not
enter apostrophe's in the box) on the form, what type of error handler can
help me? Thank you in advance!!

Here is the code: (i get the error when an apostrophe is entered into rst1

Do Until rst2.EOF
strSQL = "UPDATE [PARStoreData_tbl] SET [Comments] = """ & rst1.
Fields("Comments").Value & """" & _
" WHERE [PARStoreData_tbl].[QAID] = """ & rst.Fields
("NewQAID").Value & rst2.Fields("Comments").Value & """"
DoCmd.RunSQL strSQL
rst2.MoveNext
Loop

 

[Douglas J. Steele]

Is that really your code? Using three double quotes in a row should work
fine with apostrophes: it's only if you're using single quotes as a
delimiter that you should run into problems.

 

[auujxa2 via AccessMonster.com]

Yes, this is my code. Why do you ask? Do you have an easier way?

Is that really your code? Using three double quotes in a row should work
fine with apostrophes: it's only if you're using single quotes as a
delimiter that you should run into problems.

I have a strSQL that gives me syntax errors, when someone enters an
apostrophe in the text box as a value. (ie. Men's Clothing)

[quoted text clipped - 13 lines]

rst2.MoveNext
Loop

 

[Sylvain Lafontaine]

Is this the real code? Like Douglas has said, by using the double quote as
the delimiter, you shouldn't have any problem with embedded single quotes.
However, it's possible that some of your users are using the double quote on
their keyboard instead of the single quote for the apostrophe. When using a
string delimiter, you should always make sure that any embedded delimiter is
doubled when building your sql string by using the Replace function. In the
case of the double quote " as the string delimiter:

.... SET [Comments] = """ & Replace (rst1.Fields("Comments").Value, """",
"""""") ...

or:
.... SET [Comments] = """ & Replace (rst1.Fields("Comments").Value, '"',
'""') ...

or:
.... SET [Comments] = """ & Replace (rst1.Fields("Comments").Value, chr(34),
chr(34) & chr(34)) ...

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[auujxa2 via AccessMonster.com]

Gentlemen, I'm sorry. I posted the wrong part of the code. The code giving
me the syntax was the code that ran PRIOR to the code I originally posted:

strSQL = "INSERT INTO [YesComments] " & "([Comments])" & _
" VALUES( '" & Me.txtYesComments & " - " & Me.txtYesDate & "');"

Is that really your code? Using three double quotes in a row should work
fine with apostrophes: it's only if you're using single quotes as a
delimiter that you should run into problems.

I have a strSQL that gives me syntax errors, when someone enters an
apostrophe in the text box as a value. (ie. Men's Clothing)

[quoted text clipped - 13 lines]

rst2.MoveNext
Loop

 

[auujxa2 via AccessMonster.com]

I'm sorry about posting the wrong code and wasting your time. I removed the
single quote, and added doubles, and it works fine.

thank you again

strSQL = "INSERT INTO [YesComments] " & "([Comments])" & _
" VALUES( """ & Me.txtYesComments & Me.txtYesDate & """);"

Is this the real code? Like Douglas has said, by using the double quote as
the delimiter, you shouldn't have any problem with embedded single quotes.
However, it's possible that some of your users are using the double quote on
their keyboard instead of the single quote for the apostrophe. When using a
string delimiter, you should always make sure that any embedded delimiter is
doubled when building your sql string by using the Replace function. In the
case of the double quote " as the string delimiter:

... SET [Comments] = """ & Replace (rst1.Fields("Comments").Value, """",
"""""") ...

or:
... SET [Comments] = """ & Replace (rst1.Fields("Comments").Value, '"',
'""') ...

or:
... SET [Comments] = """ & Replace (rst1.Fields("Comments").Value, chr(34),
chr(34) & chr(34)) ...

I have a strSQL that gives me syntax errors, when someone enters an
apostrophe in the text box as a value. (ie. Men's Clothing)

[quoted text clipped - 13 lines]

rst2.MoveNext
Loop

 

[John W. Vinson]

Gentlemen, I'm sorry. I posted the wrong part of the code. The code giving
me the syntax was the code that ran PRIOR to the code I originally posted:

strSQL = "INSERT INTO [YesComments] " & "([Comments])" & _
" VALUES( '" & Me.txtYesComments & " - " & Me.txtYesDate & "');"

Just do the same double-doublequote fix as in the other query:

strSQL = "INSERT INTO [YesComments] ([Comments])" & _
" VALUES( """ & Me.txtYesComments & " - " & Me.txtYesDate & """);"