Use of Double quotes and apostrophe

[Jerry315]

I have a text box that the user can input their own text
that may have both a double quote mark and an apostrophe
as part of the text. I use the following insert statement
to add the data, however it will not allow for double
quotes - is there a way to allow for both, a "quote" and
an apostrophe? The field is defined as a memo type.

DoCmd.RunSQL "INSERT INTO [KeyTheme] (Organization,
ThemeType, ThemeDesc ) " & _
"VALUES(""" & Forms!SelOrg!SelOrg & """, " & _
"1,""" & txtUserStrength & """) WITH OWNERACCESS OPTION"

Thanks in advance!

 

[Douglas J. Steele]

Decide what you want to use as a delimiter (either " or '), and double any
occurrence of that character in your text. Assuming you're using Access 2000
or higher, you can use the Replace function to accomplish this doubling.

For example, to insert the phrase Jerry's "Vision", you could use either of

strPhrase = "Jerry's ""Vision"""

DoCmd.RunSQL "INSERT INTO Table (Field) " & _
"VALUES(""" & Replace(strPhrase, """", """""") & """)"

or

DoCmd.RunSQL "INSERT INTO Table (Field) " & _
"VALUES('" & Replace(strPhrase, "'", "''") & "')"

Exagerated for clarity,

DoCmd.RunSQL "INSERT INTO Table (Field) " & _
"VALUES(" " " & Replace(strPhrase, " " " ", " " " " " ") & " " ")"

or

DoCmd.RunSQL "INSERT INTO Table (Field) " & _
"VALUES( ' " & Replace(strPhrase, " ' ", " ' ' ") & " ' )"

 

[Tom Wickerath]

Hi Jerry,

See the following KB article as a starting point:

HOWTO: Handle Quotes and Pipes in Concatenated SQL Literals
http://support.microsoft.com/?id=178070

This KB article only discusses SELECT queries. I have a sample Access database with code from
this article that I modified to handle an INSERT INTO (ie. Append) query. If you send me a
private e-mail message that includes a valid e-mail address, I will send you a zipped copy of
this database. (Sorry, but I've been too lazy so far to create a website to post the sample
to--maybe some day I'll get around to doing that).

Tom
AOS168 AT Comcast DOT Net
______________________________________


I have a text box that the user can input their own text
that may have both a double quote mark and an apostrophe
as part of the text. I use the following insert statement
to add the data, however it will not allow for double
quotes - is there a way to allow for both, a "quote" and
an apostrophe? The field is defined as a memo type.

DoCmd.RunSQL "INSERT INTO [KeyTheme] (Organization,
ThemeType, ThemeDesc ) " & _
"VALUES(""" & Forms!SelOrg!SelOrg & """, " & _
"1,""" & txtUserStrength & """) WITH OWNERACCESS OPTION"

Thanks in advance!