MSQuery: Error Using Date in Criteria Expression in Query

ID: Q107196

The information in this article applies to:

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

Additional query words: 5.00 


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

Last Reviewed: April 9, 1999