MSQUERY: Unexpected Single Quotation Marks Added to Criteria

 Last reviewed: September 12, 1996
Article 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" (without the quotation marks) 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

KBCategory: kbtool
KBSubcategory: xlquery

Additional reference words: 1.00 2.00 5.00 5.00c



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.