Microsoft KB Archive/124664

= Unexpected Single Quotation Marks Added to Criteria =

Article ID: 124664

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Query 2000

-



This article was previously published under Q124664



SYMPTOMS
In Microsoft Query, if you add a text string to an existing text string in the Value field in the Criteria pane, and you add the new text outside of the single quotation marks surrounding the existing string, a large number of quotation marks are added to the Value field. Additionally, the criteria that you use may not return the records that you are expecting.



CAUSE
This behavior is by design of Microsoft Query. When you enter a text string in the Value row of the Criteria pane, and you do not include quotation marks around the string, single quotation marks are automatically placed around the string.

When you enter a string in the Value row of the Criteria pane, and the string contains a single quotation mark, again single quotation marks are automatically added around the string, and a single quotation mark is added to the quotation mark inside of the string, so that it is not interpreted as the final quotation mark.

When you add another text string to the beginning of a quoted string, the quotation marks that surrounded the old string become part of the new string, which is surrounded by quotation marks. Additionally, quotation marks inside of the string are doubled.

For example, if you type the string abc in the Value field, the string appears as 'abc' when you press ENTER. If you then add the character a to the beginning of the value, outside of the quotation marks, the string appears as 'aabc' when you press ENTER.



MORE INFORMATION
The following expression:

'aabc'

shows records where Value equals:

a'abc'

The following expression:

'a''abc'

shows records where Value equals:

a'abc

Note that Microsoft Query Help documents that although Microsoft Query automatically adds single quotation marks (' ') around text that you enter in the Criteria pane, it's a good idea to enter the single quotation marks yourself to make sure that Microsoft Query correctly recognizes values as text.

