MSQUERY: Unexpected Single Quotation Marks Added to Criteria

ID: Q124664

The information in this article applies to:

Microsoft Query for Windows, version 1.0
Microsoft Query for Windows 95, version 2.0

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 'a''abc''' when you press ENTER.

MORE INFORMATION

The following expression:

'a''abc'''

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.

REFERENCES

For an Expressions Overview, choose the Search button in Help, and type:

criteria expressions

Additional query words: 5.00 5.00c

Keywords : xlquery
Version : 1.00 2.00
Platform : WINDOWS
Issue type :