The use of special characters with the Jet database engine
This article explains how we get around problems with the use of special characters in database work with the MS Jet Database engine.
Our Microsoft Jet database manual has the Jet BNF form of their version of SQL-2. It says that (a) character string literals may use either " or ' as the delimiter; and (b) that any special symbol may be used if preceded by the escape character.
Single quotation marks ('), double quotation marks ("), and the pipe symbol () are all special characters in Microsoft Jet SQL. Double up the quotation marks or, if you have only one type of quotation mark (single or double) in your data, use the other one as the delimiter. All other characters are handled as is.
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.
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'
Published Thursday, June 03, 2004
