HOWTO: Query for Literal Special Characters in a Where Clause

ID: Q147687

The information in this article applies to:

Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, version 6.0
Microsoft Visual Basic Professional and Enterprise Editions, 16-bit only, for Windows, version 4.0

SUMMARY

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.

MORE INFORMATION

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 Marks

For 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:

Use double quotation marks to delimit the literal string.

Place two single quotation marks next to each other. The Jet database engine interprets these two single quotation together as one.

Embed the Chr() function; Chr(39) is the single quotation mark and Chr(34) is the double quotation mark.

Below are examples of each method:


   [Last Name]="O'Conner"
   [Last Name]='O''Conner'
   [Last Name]='O' & Chr(39) & 'Conner'

Choosing a Method and Implementing It for Single Quotation Marks

As 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:


   criteria$ = "[Last Name]=" & Chr(34) & "O'Conner" & Chr(34)
   criteria$ = "[Last Name]=""O'Conner"""
   criteria$ = "[Last Name]='O''Conner'"
   criteria$ = "[Last Name]='O' & Chr(39) & 'Conner'"

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 Marks

When querying for a string containing a double quotation mark, use the same rules, just interchange double quotation marks for single:

Use single quotation marks to delimit the literal string.

Place two double quotation marks next to each other. The Jet database engine interprets these as one double quotation mark.

Embed the Chr() function. Chr(34) is the double quotation mark.

Below are examples of each method:


   [Famous Quotes]='"To Be or Not To Be"'
   [Famous Quotes]="""To Be or Not To Be"""
   [Famous Quotes]=Chr(34) & "To Be or Not To Be" & Chr(34)

Choosing a Method and Implementing it for Double Quotation Marks

Here's how to implement these methods in Visual Basic:


   criteria$ = "[Famous Quotes]='""To Be or Not To Be""'"
   criteria$ = "[Famous Quotes]=""""""To Be or Not To Be"""""""
   criteria$ = "[Famous Quotes]=Chr(34) & ""To Be or Not To Be"" & Chr(34)"

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:

One of the three is a delimiter.

The other two are seen as one double quotation mark to search for in the literal string.

The final example embeds the Chr(34), which Jet uses as the delimiter, then Visual Basic interprets the two double quotation marks and embeds one in the string.

Dealing with Strings that Contain Both Single and Double Quotation Marks

Further 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:

Single quotation mark as the delimiter:


   [Height]='5''10"'                 ' Two single quotes embed as one
   [Height]='5' & Chr(39) & '10"'    ' Use Chr(39) to embed a single quote

Double quotation mark as the delimiter:


   [Height]="5'10"""                 ' Two double quotes embed as one
   [Height]="5'10" & Chr(34)         ' Use Chr(34) to embed a double quote

In Visual Basic, implement the above examples in a FindFirst method:

Single quotation mark as the delimiter:


   criteria$ = "[Height]='5''10""'"
   criteria$ = "[Height]='5' & Chr(39) & '10""'"

Double quotation mark as the delimiter:


   criteria$ = "[Height]=""5'10"""""""
   criteria$ = "[Height]=""5'10"" & Chr(34)"

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'"

Additional query words: kbVBp400 kbVBp600 kbdse kbDSupport kbVBp

Keywords : kbGrpVBDB
Version :
Platform : WINDOWS
Issue type : kbhowto