Quotations marks in an INSERT INTO statement string

#11

"franc sutherland" <franc.sutherland (AT) googlemail (DOT) com> wrote

On Sep 27, 6:48 pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:

Quote:

"franc sutherland" <franc.sutherl... (AT) googlemail (DOT) com> wrote in message

news:949bfd0e-a836-4319-ab52-6083f65c717f (AT) 26g2000yqv (DOT) googlegroups.com...
On Sep 27, 3:36 pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:

"franc sutherland" <franc.sutherl... (AT) googlemail (DOT) com> wrote in message

news:2814ddc9-b1c6-4fcf-9f3f-79d352854555 (AT) h7g2000yqn (DOT) googlegroups.com...
On Sep 27, 1:29 pm, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com
wrote:

Hi,
I'm using A2003 on Windows XP SP3

I am using an INSERT INTO statement to add entries to a temporary
table. There are three values; Activity Code, Description and Project
Type. Activity Code and Project Type don't have punctuation in them,
but the description field can containg single quotation marks (') or
double quotation marks ("). The double quotation mark breaks the
db.execute statement.

The string is built using this code:
"INSERT INTO tmpActivityCodes(ActivityCode, Description, ProjectType)"
& _
"VALUES (""" & ac_new & """,""" & desc_new & """,""" & pt_new &
""");"

Debug.print of this gives:
INSERT INTO tmpActivityCodes(ActivityCode, Description,
ProjectType)VALUES ("101010","A "New" Era","P1010");

When executing the statement above get run-time error '3075'. Syntax
error (missing operator) in query expression ""A "New" Era"".

For entries which don't have a ", the INSERT INTO statement works
fine.

How do I get round this? Is there a way to pass through the double
quotation marks? Is it easier to remove them from the variable, in
this case, desc_new?
The three variables, ac_new, desc_new and pt_new are variants, but
declaring them as strings didn't make a difference.
Sorry, itchy trigger finger, pressed the Send button before adding-

Thanks!

Franc.

Use the replace function to put double quotes in desc_new to replace
single.

Or, Use a saved parameter query and use DAO to fill the parameters.
Hi Ron,
Thanks for replying.
The quotation marks in desc_new are already double quotes. I've tried
doing it the other way round and replacing the double quotes with
single quotes, but couldn't get it to work.
I don't think a parameter query would work as it is not a single user
input but an import then comparison/merge with two access tables.
Would it be better to eliminate all quotes from the field?
Franc.

You would need to replace any quotes in the source string before adding to
the query string

"INSERT INTO tmpActivityCodes(ActivityCode, Description, ProjectType)" & _
"VALUES (""" & ac_new & """,""" & replace(desc_new,"""","""""") & ""","""
&
pt_new & """);"

This would take care of a " in desc_new but if the data comes from user
typing you will need to handle '*?.

Converting this insert query into a saved query, replacing desc_new with a
string type parameter would allow you to insert the description without
editing out quotes and other problem strings.

Hi Ron,
Thanks for your reply. I think I understand the string parameter into
a saved query, but I am unsure how to set it up. How do I reference
the string parameter to the query?
Can you give me some pointers?
Thanks,
Franc.

Dim dbs As Database
Dim qdf As QueryDef
Dim strSQL As String

Set dbs = CurrentDb
' Construct SQL string.
strSQL = "PARAMETERS [AC_NEW] String, [Desc_New] String, [PT_NEW] String; "
& _
"INSERT INTO tmpActivityCodes(ActivityCode, Description, ProjectType)" & _
"VALUES ([AC_NEW], [DESC_NEW], [PT_NEW]);"
' Create temp QueryDef object.
Set qdf = dbs.CreateQueryDef("", strSQL)
' Supply values for parameters.
qdf.Parameters![ac_new] = ac_new
qdf.Parameters![desc_new] = desc_new
qdf.Parameters![pt_new] = pt_new
' Execute query
qdf.Execute
Set qdf = Nothing
Set dbs = Nothing

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Quotations marks in an INSERT INTO statement string

comp.databases.ms-access comp.databases.ms-access

Re: Quotations marks in an INSERT INTO statement string - 09-28-2010 , 07:30 AM