Article ID: 147687 - View products that this article applies to.
This article was previously published under Q147687
The purpose of this article is to demonstrate how to set up the WHERE clause of an SQL statement to search for special characters in a text field of a database table.
The special characters in the Microsoft Jet Database Engine are the single quotation mark ('), double quotation mark ("), and the pipe or vertical bar (|). All the other alpha-numeric characters are treated as part of the literal string.
The single and double quotation marks are used interchangeably by the Jet database engine as delimiters for literal strings. Therefore, special consideration must be taken when you set up a Where clause to query a text field that contains embedded single or double quotation marks.
Looking for a String that Contains Single Quotation MarksFor example, if you need to query a [Last Name] field for O'Conner, the Jet database engine needs to see the single quotation mark as part of the literal, not as a delimiter. You can accomplish this three different ways:
Choosing a Method and Implementing It for Single Quotation MarksAs you can see, the first method is the easiest to read, but because Visual Basic uses double quotation marks as a string delimiter, it is not simple to implement. In a Visual Basic program, the last two options require you to know in advance whether or not a user entered a quotation mark as part of the search string; then you'd need to parse it and append the extra single quotation mark or Chr(39) function.
Keep in mind that Visual Basic itself uses double quotation marks as a string literal delimiter. The following lines of code show you how to implement the queries in a Visual Basic program to perform a "Recordset.FindFirst criteria$" operation:
The first method uses the Visual Basic Chr(34) function to embed double quotation marks in the string passed to the Jet database engine. The second method uses two double quotation marks in a row, which Visual Basic interprets and embeds as a single double quotation mark in the string. The third option passes the two single quotation marks to the Jet database engine, which interprets them as one single quotation mark. The fourth option passes the embedded Chr(39) function to the Jet database engine, which evaluates it as the single quotation mark.
Looking for a String that Contains Double Quotation MarksWhen querying for a string containing a double quotation mark, use the same rules, just interchange double quotation marks for single:
Choosing a Method and Implementing it for Double Quotation MarksHere's how to implement these methods in Visual Basic:
The first example uses two double quotation marks, so Visual Basic embeds one in the string. The second example uses six double quotation marks in a row, so Visual Basic embeds three in a row in the string. Then the Jet database engine uses those three double quotation marks as follows:
Dealing with Strings that Contain Both Single and Double Quotation MarksFurther complications arise when you need to search for a string that contains both a single and a double quotation mark. For example, if you want to search a [Height] field of a medical database for all patients over 5'10" (five feet 10 inches) tall. Here you need to decide which quotation mark to use as a delimiter. The following example shows you what the Jet database engine needs to see:
Pipe Character or Vertical BarThe 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:
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:
Article ID: 147687 - Last Review: July 1, 2004 - Revision: 2.1