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.

On Sep 27, 1:29*pm, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com>
wrote:
Sorry, itchy trigger finger, pressed the Send button before adding-

Thanks!

Franc.

On 27/09/2010 13:30:34, franc sutherland wrote:
You might try

"INSERT INTO tmpActivityCodes(ActivityCode, Description, ProjectType)"
& _
*"VALUES (Chr$(34) & ac_new & Chr$(34),Chr$(34) & Replace(desc_new, '"',''')
& Chr$(34),Chr$(34) & pt_new & Chr$(34) );

Chr$(34) is a double quote and so much easier to read than """. Only problem
is that your tmpActivityCodes table will only have single quotes and no
double quotes. Is this a problem?

Phil

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

On Sep 27, 1:29 pm, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com>
wrote:
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.

On Sep 27, 1:45*pm, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
Hi Phil,

Thanks for your reply.

I swapped in your statement above and I'm afraid it didn't work.

This part was in black
"INSERT INTO tmpActivityCodes(ActivityCode, Description, ProjectType)"
& _
"VALUES (Chr$(34) & ac_new & Chr
$(34),Chr$(34) & Replace(desc_new, '"

And the next part was 'comment' in green.
',''') & Chr$(34),Chr$(34) & pt_new & Chr$(34) );

It took the part in black as the string, and the part beyond the
second double quotation mark as a comment as the next character was a
single qm '.

Franc.

On Sep 27, 1:45*pm, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
Sorry forgot to add the debug.pring on the INSERT INTO statement which
was

INSERT INTO tmpActivityCodes(ActivityCode, Description,
ProjectType)VALUES (Chr$(34) & ac_new & Chr$(34),Chr$(34) &
Replace(desc_new, '

Franc.

On Sep 27, 3:36*pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:
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.

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

On Sep 27, 3:36 pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:
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.

On Sep 27, 6:48*pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:
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.

franc sutherland wrote:
PMFJI.
Parameters make this whole mess so simple. Start by creating your sql
string:

dim sql as string
sql= "INSERT INTO " & _
"tmpActivityCodes(ActivityCode, Description, ProjectType)" & _
"VALUES([pAc],[pDesc],[pType])"

The bracketed strings will be treated as parameters given that there are no
database objects within scope of the query that have the same name as the
strings in the brackets: hence my SOP of prefixing all the parameter names
with "p".

Continue by instantiating a querydef object using the above string:

dim qdf as dao.querydef
set qdf=currentdb.createquerydef("",sql)
'using an empty string causes a temp querydef to be created

Set the parameter values:
qdf(0)=ac_new
qdf(1)=desc_new
qdf(2)=pt_new

Execute the query (you probably want to include an error handler):
qdf.execute


So now your only "problem" is getting the string containing the quotes into
that desc_new variable, which it appears you've already solved.