MSQUERY: Selected Value(s) Dialog Box Contains Null Values

Last reviewed: September 12, 1996
Article ID: Q136398

The information in this article applies to:

Microsoft Query for Windows, version 1.0

SYMPTOMS

In Microsoft Query, when you use the Values button in the Edit Criteria dialog box to select a value for a criteria, the Values list in the Select Value(s) dialog box may contain a list of null (empty) values.

CAUSE

This behavior occurs when you use a complex expression that evaluates to a null value, such as the following expression:

   Left(<column name>, -2)

Normally, when you use a criteria expression that evaluates to null, for example, using a field that does not contain any values, the Values list in the Select Value(s) dialog box is empty, and you cannot select any items in the list. However, if you use a criteria expression such as the example shown above, the Values list in the Select Value(s) dialog box contains empty items that you can select using the mouse; the number of empty items corresponding to the number of records in the database.

Note that this behavior occurs using the following data sources (data sources that use the SIMBA.DLL driver):

   Btrieve
   Access
   Paradox
   Excel
   dBASE
   FoxPro

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Query for Windows 95, version 2.0.

MORE INFORMATION

You can use functions such as Left and Right in a criteria expression, to return part of the characters in a field. For example, if you use the following expression in a Criteria Field in the Criteria pane

   Left(Name,2)

you can compare any value to the leftmost 2 characters of the values in the Name field. Only the records with the values in the Name field whose leftmost 2 characters equal the value you set in the criteria are returned.

REFERENCES

For more information about the Left and Left$ Functions, choose the Search button in Microsoft Access 2.0 Help and type:

    Left

KBCategory: kbtool
KBSubcategory: xlquery
Additional reference words: 1.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.