Microsoft KB Archive/107196
MSQuery: Error Using Date in Criteria Expression in Query
The information in this article applies to:
- Microsoft Query for Windows, version 1.0
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.
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.
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
- In Microsoft Query, open an existing query or create a new query that retrieves data from a table that contains a field with dates.
- 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.
- Select the date field from the table and drag it to the data pane.
- From the Criteria menu, choose Add Criteria.
- In the Field box, enter "Date + 3" (without the quotation marks) where "Date" is the name of the field containing the dates.
- From the Operators box, select Equals.
- Choose Values, and from the Values list, select a date and choose OK.
- 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.
"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:
Additional query words: 5.00
Keywords : xlquery
Version : 1.00
Platform : WINDOWS
Issue type :
Last Reviewed: April 9, 1999