You are currently offline, waiting for your internet to reconnect

How To Query for Literal Special Characters in a Where Clause

This article was previously published under Q147687
SUMMARY
The purpose of this article is to demonstrate how to set up the WHEREclause of an SQL statement to search for special characters in a text fieldof a database table.

The special characters in the Microsoft Jet Database Engine are the singlequotation mark ('), double quotation mark ("), and the pipe or vertical bar(|). All the other alpha-numeric characters are treated as part of theliteral string.
MORE INFORMATION
The single and double quotation marks are used interchangeably by the Jetdatabase engine as delimiters for literal strings. Therefore, specialconsideration must be taken when you set up a Where clause to query a textfield 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 Jetdatabase engine needs to see the single quotation mark as part of theliteral, 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 VisualBasic uses double quotation marks as a string delimiter, it is not simpleto implement. In a Visual Basic program, the last two options require youto know in advance whether or not a user entered a quotation mark as partof the search string; then you'd need to parse it and append the extrasingle quotation mark or Chr(39) function.

Keep in mind that Visual Basic itself uses double quotation marks as astring literal delimiter. The following lines of code show you how toimplement 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 doublequotation marks in the string passed to the Jet database engine. The secondmethod uses two double quotation marks in a row, which Visual Basicinterprets and embeds as a single double quotation mark in the string. Thethird option passes the two single quotation marks to the Jet databaseengine, which interprets them as one single quotation mark. The fourthoption 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 samerules, 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 embedsone in the string. The second example uses six double quotation marks in arow, so Visual Basic embeds three in a row in the string. Then the Jetdatabase 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, thenVisual Basic interprets the two double quotation marks and embeds one inthe string.

Dealing with Strings that Contain Both Single and Double Quotation Marks

Further complications arise when you need to search for a string thatcontains both a single and a double quotation mark. For example, if youwant to search a [Height] field of a medical database for all patients over5'10" (five feet 10 inches) tall. Here you need to decide which quotationmark to use as a delimiter. The following example shows you what the Jetdatabase 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 Jetdatabase engine. It tells the Jet database engine to evaluate theidentifier before evaluating the rest of the expression. Therefore, the Jetdatabase engine inserts the value of the identifier in the expression, andthen evaluates it.

Vertical bars are used most often in domain aggregate functions when youwant the function to automatically recalculate the value it returns infilters. Or vertical bars are used as an alternative to the ampersand (&)operator when you concatenate text values. Because of this, you cannotembed 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 Jetdatabase 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 theFindFirst method:
   criteria$ = "'Yes' & Chr(124) & 'No'"				
kbVBp400 kbVBp600 kbdse kbDSupport kbVBp
Properties

Article ID: 147687 - Last Review: 07/01/2004 21:30:00 - Revision: 2.1

  • Microsoft Visual Basic 6.0 Learning Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 6.0 Enterprise Edition
  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 4.0 16-bit Enterprise Edition
  • kbhowto KB147687
Feedback