'|' in sql query gives - Error 3075 - 'Invalid use of vertical bars...'

Terry D. Myers

unread,

Apr 21, 1997, 3:00:00 AM4/21/97

to

How are pipes (|) used in JET query expressions and how do I get a jet
query to treat it like normal text?? I know that you have to trap out
single ticks (') by doubling them up - how do you handle pipes? Are there
any other special characters that you have to watch out for when passing
them through the JET engine as part of a query?

Thanks in advance,
T.

---------------------------------------------
* Terry Myers - Senior Systems Engineer *
* Halo Technologies *
* OKC - 405.330.2400 Tulsa - 918.585.2207 *
* tdm...@halocorp.com or tdm...@ionet.net *
---------------------------------------------

Joe Space Invader

unread,

Apr 22, 1997, 3:00:00 AM4/22/97

to

"Terry D. Myers" <tdm...@ionet.net> writes:

> '|' in sql query gives - Error 3075 - 'Invalid use of vertical bars...'

> How are pipes (|) used in JET query expressions and how do I get a jet
> query to treat it like normal text?? I know that you have to trap out
> single ticks (') by doubling them up - how do you handle pipes? Are there
> any other special characters that you have to watch out for when passing
> them through the JET engine as part of a query?

> Thanks in advance,
> T.

A quick test gives me the same error. I get a different error if I double
up the bars, and an unset parameter error if I have two bars with any text
between them. The only thing that worked for me was if I created a
parameterized QueryDef and passed the string containing the bars to the
parameter, like so:

Query [this sucks]:
PARAMETERS meef Text;
SELECT DISTINCTROW templates.*
FROM Templates
WHERE descr = [meef];

Sub F___YouVeryMuchMicrosoft (DB As Database)
Dim QD As QueryDef: Set QD = DB.OpenQueryDef("this sucks")
QD!meef = "|what a load of crap|"
Dim SS As Snapshot: Set SS = QD.CreateSnapshot()
While (SS.EOF = False)
Debug.Print SS!Descr
SS.MoveNext
Wend
SS.Close : Set SS = Nothing
QD.Close : Set QD = Nothing
End Sub

Why, Microsoft, WHY??????? It would be one thing if we could at escape the
vertical bars like we can single and double-quotes, but this...!

--
Joe Foster (j...@bftsi0.gate.net or jfo...@gate.net)
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!

Joe Space Invader

unread,

Apr 22, 1997, 3:00:00 AM4/22/97

to

Joe "Space Invader" Foster <j...@bftsi0.UUCP> writes:

> Why, Microsoft, WHY??????? It would be one thing if we could at escape the
> vertical bars like we can single and double-quotes, but this...!

Sorry to follow up on my own post, but it gets even worse! I even got
errors when I tried to get around the problem using LIKE:

Set x = DB.CreateSnapshot("select * from templates where descr like '[|]nothing works[|]';")

Well, this might explain some weird errors I've heard about, perhaps caused
by users entering a vertical bar into a search string box by accident. OK,
who can I complain to at Microsoft without getting hit with an incident fee?
This is definitely a bug!

Terry Myers

unread,

Apr 22, 1997, 3:00:00 AM4/22/97

to

After looking for a needle in a haystack for two days I found this in the
Access Help file by doing a search with the answer wizard on 'Vertical Bar'
and then going to 'Using Values in Expressions' (Obvious right?)...
-------------
In some circumstances - for example, in a domain aggregate function such as
DLookup - the value of the field, control, or property must appear in
single or double quotation marks. The easiest way to accomplish this is to
add a single quotation mark (') to the literal string, and then combine the
expression with another literal string made up of a single quotation mark
after the field, control, or property value, as follows:

"[CategoryID] = ' " & Forms![Products]![CategoryID] & " ' "

Existing Microsoft Access applications may use the vertical bar operators
(| |) in place of an opening and closing combination of double quotation
marks (") and & (concatenation) operators, as follows:
"[CategoryID] = '|Forms![Products]![CategoryID]|' "
However, the use of vertical bars is not recommended because they can
produce unexpected results in some circumstances.
-------------

Huh?!? I would say unexpected results in some circumstances...

T.

---------------------------------------------
* Terry Myers - Senior Systems Engineer *
* Halo Technologies *
* OKC - 405.330.2400 Tulsa - 918.585.2207 *
* tdm...@halocorp.com or tdm...@ionet.net *
---------------------------------------------

> > Why, Microsoft, WHY??????? It would be one thing if we could at escape

> the
> > vertical bars like we can single and double-quotes, but this...!
>