Error in insert query

[John]

Hi

When I run the following query in Access 97 I get syntax error at %;

INSERT INTO [MyTable] ( MyField )
SELECT "Location=|British%20Museum"

If I take out the % I still an error which is only resolved by taking out
the | character. What is the problem and how can I get round this?

Thanks

Regards

 

[Piet Linden]

Hi

When I run the following query in Access 97 I get syntax error at %;

INSERT INTO [MyTable] ( MyField )
SELECT "Location=|British%20Museum"

If I take out the % I still an error which is only resolved by taking out
the | character. What is the problem and how can I get round this?

Thanks

Regards

I find the SQL puzzling...
if you're not getting the data from a table, you don't need SELECT but
VALUES

INSERT INTO [MyTable] ( MyField )
VALUES ('Location=British Museum')

the = makes me think you're trying to filter something... so explain
what you're trying to do and maybe someone can sort out what's wrong.

 

[John]

Hi

This query is made on the fly as a string. Also '=' is just to show what
value the location field has. I can use any other character instead if it
fixes the issue. Actually the value being assigned is a url (which I have
trimmed down for ease) of a map location . I really need the | character in
string if possible.

Thanks

Regards

Hi

When I run the following query in Access 97 I get syntax error at %;

INSERT INTO [MyTable] ( MyField )
SELECT "Location=|British%20Museum"

If I take out the % I still an error which is only resolved by taking out
the | character. What is the problem and how can I get round this?

Thanks

Regards

I find the SQL puzzling...
if you're not getting the data from a table, you don't need SELECT but
VALUES

INSERT INTO [MyTable] ( MyField )
VALUES ('Location=British Museum')

the = makes me think you're trying to filter something... so explain
what you're trying to do and maybe someone can sort out what's wrong.

 

[John]

INSERT INTO [MyTable] ( MyField )
VALUES( "Location=|British%20Museum")

also gives error. If I take out | it works fine.

Thanks

Regards

Hi

When I run the following query in Access 97 I get syntax error at %;

INSERT INTO [MyTable] ( MyField )
SELECT "Location=|British%20Museum"

If I take out the % I still an error which is only resolved by taking out
the | character. What is the problem and how can I get round this?

Thanks

Regards

I find the SQL puzzling...
if you're not getting the data from a table, you don't need SELECT but
VALUES

INSERT INTO [MyTable] ( MyField )
VALUES ('Location=British Museum')

the = makes me think you're trying to filter something... so explain
what you're trying to do and maybe someone can sort out what's wrong.

 

[Tony Toews [MVP]]

INSERT INTO [MyTable] ( MyField )
VALUES( "Location=|British%20Museum")

also gives error. If I take out | it works fine.

The | or pipe character appears to mean something special to Jet SQL.
See page 13 of
http://www.insomniasec.com/publications/Access-Through-Access.pdf.
That page states "This error message is caused when the vertical bar
is placed outside of surrounding quotes" so I'd try that.

If that doesn't work then try surrounding it with square brackets.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

When I run the following query in Access 97 I get syntax error at %;

INSERT INTO [MyTable] ( MyField )
SELECT "Location=|British%20Museum"

What is interesting is I don't get this error in A2000 or A2003. I
don't have A97 installed and I don't feel like installing it just to
test for this. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

When I run the following query in Access 97 I get syntax error at
%;

INSERT INTO [MyTable] ( MyField )
SELECT "Location=|British%20Museum"

What is interesting is I don't get this error in A2000 or A2003.
I don't have A97 installed and I don't feel like installing it
just to test for this.

Whenever one has problems like this, it's easy to just replace the
literal character with the Chr() value for it, which would be
Chr(124):

INSERT INTO [MyTable] ( MyField )
SELECT "Location=" & Chr(124) & "British%20Museum"