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

ID: Q136398

1.00 WINDOWS kbtool

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

Keywords          : xlquery 
Version           : 1.00
Platform          : WINDOWS

Last Reviewed: September 15, 1996