Microsoft KB Archive/107196: Difference between revisions
(importing KB archive) |
m (Text replacement - ">" to ">") |
||
(2 intermediate revisions by the same user not shown) | |||
Line 24: | Line 24: | ||
<blockquote><div class="ERRORMESSAGE"> | <blockquote><div class="ERRORMESSAGE"> | ||
Error in predicate: | Error in predicate: <criteria field><criteria operator><criteria value> Incompatible types in predicate. | ||
</div></blockquote> | </div></blockquote> | ||
where | 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. | ||
<br /> | <br /> | ||
Line 41: | Line 41: | ||
== MORE INFORMATION == | == MORE INFORMATION == | ||
A criteria value such as | 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.<br /> | ||
<br /> | <br /> | ||
Line 55: | Line 55: | ||
# Select the date field from the table and drag it to the data pane. | # Select the date field from the table and drag it to the data pane. | ||
# From the Criteria menu, choose Add Criteria. | # From the Criteria menu, choose Add Criteria. | ||
# In the Field box, enter ''' | # 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. | # From the Operators box, select Equals. | ||
# Choose Values, and from the Values list, select a date and choose OK. | # Choose Values, and from the Values list, select a date and choose OK. | ||
Line 64: | Line 64: | ||
<blockquote><div class="ERRORMESSAGE"> | <blockquote><div class="ERRORMESSAGE"> | ||
Error in predicate: Date+3= | Error in predicate: Date+3=<criteria value> Incompatible types in predicate. | ||
</div></blockquote> | </div></blockquote> | ||
where | where <criteria value> is the value selected from the Values list in step 7. | ||
<br /> | <br /> | ||
Line 74: | Line 74: | ||
== REFERENCES == | == REFERENCES == | ||
"Microsoft Query User's Guide," version 5.0, pages 69-70<br /> | |||
<br /> | <br /> | ||
For more information about Criteria Expressions (Cue Cards) or Expressions Overview, choose the Search button in Help and type:<br /> | For more information about Criteria Expressions (Cue Cards) or Expressions Overview, choose the Search button in Help and type:<br /> |
Latest revision as of 17:44, 20 July 2020
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: <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
- 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.
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 :
Technology :
Last Reviewed: April 9, 1999 |