Imbedded quotes in text field

[Linda]

I have the following line of code
strsql = "INSERT INTO ProgMgmtTbl (PA, Item, Issue, Current_Status,
Closure_Plan) values('" _
& [SR Number] & " ', '" & [SR Title] & "',' " & Description & "
' , ' " & Status & " ' , ' " & Resolution & " ' )"

which I changed after reading other postings to :

strsql = "INSERT INTO ProgMgmtTbl (PA, Item, Issue, Current_Status,
Closure_Plan) values('" & [SR Number] & " ', '" & [SR Title] & "',""" &
Description & """, """ & Status & """, """ & Resolution & """)"

This works to avoid errors when the text fields contain a single quote. But
I also have the case where a word within the field might be enclosed in
double quotes. How do I get those to pass through as well?
Thanks for your help.

 

[Ken Snell [MVP]]

You're delimiting with the ' character, so embedded " character will not
cause a problem.

 

[Linda]

Can you suggest a debug tool that will help me identify what is causing my
error? The error output looks something like this...

Run-time error '3075'
Syntax error (missing operator) in query expression '''[06-01-2009:djt] Some
text.

[6/8/09-JDC] 1) Need a "drop dead" date 2) Send formal request.'''.

I was guessing at it being the double quotes. But if that's not it, what is?
Thanks,



--
Linda

You're delimiting with the ' character, so embedded " character will not
cause a problem.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have the following line of code
strsql = "INSERT INTO ProgMgmtTbl (PA, Item, Issue, Current_Status,
Closure_Plan) values('" _
& [SR Number] & " ', '" & [SR Title] & "',' " & Description & "
' , ' " & Status & " ' , ' " & Resolution & " ' )"

which I changed after reading other postings to :

strsql = "INSERT INTO ProgMgmtTbl (PA, Item, Issue, Current_Status,
Closure_Plan) values('" & [SR Number] & " ', '" & [SR Title] & "',""" &
Description & """, """ & Status & """, """ & Resolution & """)"

This works to avoid errors when the text fields contain a single quote.
But
I also have the case where a word within the field might be enclosed in
double quotes. How do I get those to pass through as well?
Thanks for your help.

 

[John W. Vinson]

This works to avoid errors when the text fields contain a single quote. But
I also have the case where a word within the field might be enclosed in
double quotes. How do I get those to pass through as well?
Thanks for your help.

If you need to insert text which may contain either ' or " characters within
the string, then you'll need to "double up" the delimiter you're using. Try

strsql = "INSERT INTO ProgMgmtTbl (PA, Item, Issue, Current_Status,
Closure_Plan) values('" & [SR Number] & " ', '" & [SR Title] & "',""" &
Replace(Description, """", """""") & """, """ & Status & """, """ & Resolution
& """)"

to insert a Description containing doublequote characters. The actual executed
SQL should contain

"[6/8/09-JDC] 1) Need a ""drop dead"" date 2) Send formal request."

The pair of doublequotes will be translated to a single doublequote on insert.

 

[Linda]

Thank you! That worked.
--
Linda

This works to avoid errors when the text fields contain a single quote. But
I also have the case where a word within the field might be enclosed in
double quotes. How do I get those to pass through as well?
Thanks for your help.

If you need to insert text which may contain either ' or " characters within
the string, then you'll need to "double up" the delimiter you're using. Try

strsql = "INSERT INTO ProgMgmtTbl (PA, Item, Issue, Current_Status,
Closure_Plan) values('" & [SR Number] & " ', '" & [SR Title] & "',""" &
Replace(Description, """", """""") & """, """ & Status & """, """ & Resolution
& """)"

to insert a Description containing doublequote characters. The actual executed
SQL should contain

"[6/8/09-JDC] 1) Need a ""drop dead"" date 2) Send formal request."

The pair of doublequotes will be translated to a single doublequote on insert.

 

[Ken Snell [MVP]]

My apologies, Linda. I somehow missed that you were setting a string value
to a variable.... I can only plead tiredness!

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thank you! That worked.
--
Linda

This works to avoid errors when the text fields contain a single quote.
But
I also have the case where a word within the field might be enclosed in
double quotes. How do I get those to pass through as well?
Thanks for your help.

If you need to insert text which may contain either ' or " characters
within
the string, then you'll need to "double up" the delimiter you're using.
Try

strsql = "INSERT INTO ProgMgmtTbl (PA, Item, Issue, Current_Status,
Closure_Plan) values('" & [SR Number] & " ', '" & [SR Title] & "',""" &
Replace(Description, """", """""") & """, """ & Status & """, """ &
Resolution
& """)"

to insert a Description containing doublequote characters. The actual
executed
SQL should contain

"[6/8/09-JDC] 1) Need a ""drop dead"" date 2) Send formal request."

The pair of doublequotes will be translated to a single doublequote on
insert.