Funny character error in query

[John]

Hi

When I run below query in Access 97 I get a ' Syntax error in expression
"|test" ' error.

INSERT INTO [MyTable] ( MyField )
VALUES( "|test")

However if I take the | character out it runs fine. What is the problem and
how can I include the | character in the insert value?

Thanks

Regards

 

[Jerry Whittle]

It works just fine in Access 2007. Maybe it's time to upgrade! Just kidding
- about the upgrade.

I created a table and field just like your SQL statement and it ran just
fine. Next I tried using single quotes and it also worked.

Maybe A97 sees the pipe as something else. Therefore I tried it like below
and it worked in A07. Give it a try to see what happens.

INSERT INTO [MyTable] ( MyField )
VALUES( "|" & "test")

 

[John]

Hi

Didn't work. Used both double and single quotes. The pipe character seems to
be the culprit.

Thanks

Regards

It works just fine in Access 2007. Maybe it's time to upgrade! Just
kidding
- about the upgrade.

I created a table and field just like your SQL statement and it ran just
fine. Next I tried using single quotes and it also worked.

Maybe A97 sees the pipe as something else. Therefore I tried it like below
and it worked in A07. Give it a try to see what happens.

INSERT INTO [MyTable] ( MyField )
VALUES( "|" & "test")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi

When I run below query in Access 97 I get a ' Syntax error in expression
"|test" ' error.

INSERT INTO [MyTable] ( MyField )
VALUES( "|test")

However if I take the | character out it runs fine. What is the problem
and
how can I include the | character in the insert value?

Thanks

Regards

 

[John W. Vinson]

Hi

When I run below query in Access 97 I get a ' Syntax error in expression
"|test" ' error.

INSERT INTO [MyTable] ( MyField )
VALUES( "|test")

However if I take the | character out it runs fine. What is the problem and
how can I include the | character in the insert value?

Thanks

Regards

Something is very odd here. I was curious enough to test it myself, using the
exact string with London that you posted in the other thread. Using A2003, it
worked fine and inserted the pipe-containing string with no quibbles. It's not
Autocorrect because that should apply only to data being typed into a field,
but it might not hurt to check Tools... Options... Spelling... Autocorrect
Options just in case.

 

[John Spencer MVP]

I vaguely recall that Access 97 had a problem with the pile character "|". It
had some special meaning, but I can't recall what that was.

Perhaps you can get around it by using
Values("||Test")
using || to mean one | or using
Values(Chr(124) & "Test")

If I get a chance I'll see if I have 97 available and can duplicate your
problem. I do know that it is not a problem in Access 2003.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi

When I run below query in Access 97 I get a ' Syntax error in expression
"|test" ' error.

INSERT INTO [MyTable] ( MyField )
VALUES( "|test")

However if I take the | character out it runs fine. What is the problem and
how can I include the | character in the insert value?

Thanks

Regards

Something is very odd here. I was curious enough to test it myself, using the
exact string with London that you posted in the other thread. Using A2003, it
worked fine and inserted the pipe-containing string with no quibbles. It's not
Autocorrect because that should apply only to data being typed into a field,
but it might not hurt to check Tools... Options... Spelling... Autocorrect
Options just in case.

 

[John]

Hi

Chr(124) & "Test" worked. Thanks

Regards

I vaguely recall that Access 97 had a problem with the pile character "|".
It had some special meaning, but I can't recall what that was.

Perhaps you can get around it by using
Values("||Test")
using || to mean one | or using
Values(Chr(124) & "Test")

If I get a chance I'll see if I have 97 available and can duplicate your
problem. I do know that it is not a problem in Access 2003.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi

When I run below query in Access 97 I get a ' Syntax error in expression
"|test" ' error.

INSERT INTO [MyTable] ( MyField )
VALUES( "|test")

However if I take the | character out it runs fine. What is the problem
and how can I include the | character in the insert value?

Thanks

Regards

Something is very odd here. I was curious enough to test it myself, using
the
exact string with London that you posted in the other thread. Using
A2003, it
worked fine and inserted the pipe-containing string with no quibbles.
It's not
Autocorrect because that should apply only to data being typed into a
field,
but it might not hurt to check Tools... Options... Spelling...
Autocorrect
Options just in case.

 

[John W. Vinson]

I vaguely recall that Access 97 had a problem with the pile character "|". It
had some special meaning, but I can't recall what that was.

I spent a while digging around in Help, since I had the same vague
recollection. The pipe was used as some sort of "superquote" character, but I
can't find anything relevant.

 

[Peter Hibbs]

From Access 97 POWER Programming by F Scott Barker -

"Access 97 and Jet 3.5 Errors.
Some Jet errors have the | symbol in them. This symbol is used as a
placeholder for variables that are passed to the errors. An example of
this is error number 3006, Database '|' is exclusively locked., in
which the pipe would be replaced with the name of the database
exclusively locked."

Don't know if this is relevant in this situation though.

Peter Hibbs.

 

[RoyVidar]

I vaguely recall that Access 97 had a problem with the pile character
"|". It had some special meaning, but I can't recall what that was.

Perhaps you can get around it by using
Values("||Test")
using || to mean one | or using
Values(Chr(124) & "Test")

[snip]


I stumbled upon this reference and remembered reading this tread

http://support.microsoft.com/kb/178070

which confirming what you're saying

"The pipe symbol causes problems because Jet uses pipe symbols to
delimit field or parameter names embedded in a literal string, such as:

SELECT "|LastName|, |FirstName|" FROM Employees

This was considered easier for beginner users to learn than
concatenation when building ad hoc queries through the Access Query
designer. However, when used inadvertently in building a SQL statement,
it can result in an error.

The solution is to replace the pipe symbol with a concatenated
expression so that SQL contains:

SELECT * FROM SecurityLevel WHERE UID='JohnDoe'
AND PWD='A2' & chr(124) & '45'"

 

[Omar Santos]

I spent a while digging around in Help, since I had the same vague
recollection. The pipe was used as some sort of "superquote" character,
but I
can't find anything relevant.