MSQuery: Error Using Date in Criteria Expression in Query

 Last reviewed: September 12, 1996
Article ID: Q107196
The information in this article applies to:
  • Microsoft Query for Windows, version 1.0

SYMPTOMS

In Microsoft Query, when you use a date in a criteria expression, the date may not be correctly interpreted and you may receive the following error message 

   Error in predicate: <criteria field><criteria operator><criteria value>
   Incompatible types in predicate.
where <criteria field> is an expression containing a date field, <criteria operator> is the condition that must be true for the expression, and <criteria value> is the comparison value for the expression.

WORKAROUND

To avoid this error, use number signs (#) around dates in the criteria value field when you use them as the comparison value for a criteria expression. Microsoft Query does not interpret the date value correctly when the criteria field contains a date field in an expression.

MORE INFORMATION

A criteria value such as "1993-10-31" could be interpreted as either a date or an arithmetic expression. Microsoft Query only interprets an ambiguous expression to be a date when the criteria field type is a date. However, when the criteria field itself is an expression, "Date+3" for example, the date value is not correctly interpreted, and an error is generated.

Steps to Reproduce Behavior

  1. In Microsoft Query, open an existing query or create a new query that retrieves data from a table that contains a field with dates.

  2. If the table containing the dates is not displayed in the table pane, do the following to add the table:

    a. From the table menu, choose Add Tables.

    b. From the Table Name list, select the table containing the dates 

          and choose Add.

  3. Select the date field from the table and drag it to the data pane.

  4. From the Criteria menu, choose Add Criteria.

  5. In the Field box, enter "Date + 3" (without the quotation marks) where "Date" is the name of the field containing the dates.

  6. From the Operators box, select Equals.

  7. Choose Values, and from the Values list, select a date and choose OK.

  8. Choose Add.

The following error appears on the screen 

   Error in predicate: Date+3=<criteria value>
   Incompatible types in predicate.
where <criteria value> is the value selected from the Values list in step 7.

REFERENCES

"Microsoft Query User's Guide," version 5.0, pages 69-70

For more information about Criteria Expressions (Cue Cards) or Expressions Overview, choose the Search button in Help and type: 

    dates

KBCategory: kbusage kbprb kbtool
KBSubcategory: xlquery

Additional reference words: 5.00



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.