invalid use of vertical bars in expression

T

Tom Hanley

Below is a bit of code from a sub. Dwg_file contains the vertical bar key
(pipe) as a separator within the text string. When I run the sub I error
'3077' on "invalid use of vertical bars in expression" when the last line
shown below is run. I can build a query fine using design view and it
gathers all records but not when using findfirst method. I would like to
keep the vertical bar in the data field. Any ideas?

------------------
Dim MyWorkspace As Workspace, MyDatabase As Database
Dim MYSET As Recordset
Dim MYFILE As String
Dim ErrorCondition As Integer

Set MyWorkspace = DBEngine.Workspaces(0)
Set MyDatabase = MyWorkspace.Databases(0)

Dim Dwg_file, Proj_no, Prop_no As String
Proj_no = "030999"
Dwg_file = "P-0312021403|siteplan"
Prop_no = "P-0312021403"
Dim CRITERIA As String
CRITERIA = "InStr(1," & [Dwg_file] & "," & [Prop_no] & ")"
MYSET.FindFirst CRITERIA
 
G

Gary Walter

Tom Hanley said:
Below is a bit of code from a sub. Dwg_file contains the vertical bar key
(pipe) as a separator within the text string. When I run the sub I error
'3077' on "invalid use of vertical bars in expression" when the last line
shown below is run. I can build a query fine using design view and it
gathers all records but not when using findfirst method. I would like to
keep the vertical bar in the data field. Any ideas?

------------------
Dim MyWorkspace As Workspace, MyDatabase As Database
Dim MYSET As Recordset
Dim MYFILE As String
Dim ErrorCondition As Integer

Set MyWorkspace = DBEngine.Workspaces(0)
Set MyDatabase = MyWorkspace.Databases(0)

Dim Dwg_file, Proj_no, Prop_no As String
Proj_no = "030999"
Dwg_file = "P-0312021403|siteplan"
Prop_no = "P-0312021403"
Dim CRITERIA As String
CRITERIA = "InStr(1," & [Dwg_file] & "," & [Prop_no] & ")"
MYSET.FindFirst CRITERIA
Hi Tom,

I have learned to stay completely away
from the pipe symbol, so I haven't visited
this for some time. The old KB articles below
give some background on your problem.

If you have to "keep the pipe,"
then replace it in the string with

"' & chr(124) & '"

But, from your code (which I truly don't
understand what you are doing), I don't think it
matters. If you are trying to find one string in another,
then you could just as easily repace it with *any*
character that you don't expect to be in the string,
for example, a semicolon or a comma. The point being
"get rid of the pipe" in the string before running through
Jet because Jet will misinterpret it.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q178070
HOWTO: Handle Quotes and Pipes in Concatenated SQL Literals
*** quote ***
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'
<snip>
FindFirstFixup = ReplaceStr(Temp, "|", "' & chr(124) & '", 0)
*** unquote ***

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q147687
HOWTO: Query for Literal Special Characters in a Where Clause

*** quote ***
Pipe Character or Vertical Bar
The pipe character or vertical bar is a reserved character for the Jet database
engine. It tells the Jet database engine to evaluate the identifier before evaluating
the rest of the expression. Therefore, the Jet database engine inserts the value of
the identifier in the expression, and then evaluates it.

Vertical bars are used most often in domain aggregate functions when you want the
function to automatically recalculate the value it returns in filters. Or vertical
bars are used as an alternative to the ampersand (&) operator when you concatenate
text values. Because of this, you cannot embed the vertical bar (|) in a literal
string, you must embed the Chr() function. Chr(124) is the vertical bar.

For example, if you needed to search a [Response] field for Yes|No, the Jet database
engine needs to see:

[Response]='Yes' & Chr(124) & 'No'

If you try to embed the vertical bar in the string (for example,
[Response]='Yes|No'), the Jet database engine will give you a syntax error.

In Visual Basic, implement the above statement in a criteria string of the FindFirst
method:

criteria$ = "'Yes' & Chr(124) & 'No'"
*** unquote ***

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top