Microsoft KB Archive/107196

{|
 * width="100%"|

MSQuery: Error Using Date in Criteria Expression in Query

 * }

-

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:  Incompatible types in predicate.

where is an expression containing a date field, is the condition that must be true for the expression, and 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.
 * 1) Select the date field from the table and drag it to the data pane.
 * 2) From the Criteria menu, choose Add Criteria.
 * 3) In the Field box, enter "Date + 3" (without the quotation marks) where "Date" is the name of the field containing the dates.
 * 4) From the Operators box, select Equals.
 * 5) Choose Values, and from the Values list, select a date and choose OK.
 * 6) Choose Add.

The following error appears on the screen

Error in predicate: Date+3= Incompatible types in predicate.

where is the value selected from the Values list in step 7.