Imbedded quotes in text field

  • Thread starter Thread starter Linda
  • Start date Start date
L

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.
 
You're delimiting with the ' character, so embedded " character will not
cause a problem.
 
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


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

--

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


Linda said:
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.
Click to expand...
Click to expand...
 
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.
Click to expand...

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.
 
Thank you! That worked.
--
Linda


John W. Vinson said:
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.
Click to expand...

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.
Click to expand...
 
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/


Linda said:
Thank you! That worked.
--
Linda


John W. Vinson said:
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.
Click to expand...

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.
Click to expand...
Click to expand...
 
Hi,
In MS Access you can avoid the whole SQL problem and Use DAO instead. takes a bit of getting used to, but its fast and useful.
It works on local, linked and even ODBC (MySQL) Linked Tables:

Example: Add a new record to a table...

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stSome_String AS String
Set db = CurrentDb
Set rs = db.OpenRecordset("My_Table", dbOpenDynaset)
rs.AddNew
rs!My_First_Field = stSome_String
rs!Another_Field = "Some more text"
rs.Update
rs.Close

If you're new to this syntax, you put a dot between an object name and an operation... you put a ! (called a bang) between an object and something inside it (in this case, the recordset 'rs' is a table which holds a collection of fields).

for more info see the msdn pages...
http://msdn.microsoft.com/en-us/library/bb243801(v=office.12).aspx

Jonathan
 
Back
Top