HOWTO: Query for Literal Special Characters in a Where Clause

The information in this article applies to:

• Professional and Enterprise Editions of Microsoft Visual Basic, 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.

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

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.

   [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)"

• One of the three is a delimiter.
• The other two are seen as one double quotation mark to search for in the literal 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
  
  

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

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

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