Expression Too Complex in Access 2000

[Grease]

Hi,
Consider the following query:

SELECT crTbl.acct_1, crTbl.amount, crTbl.date
FROM crTbl
WHERE (((crTbl.acct_1)="Supplies") AND ((crTbl.date) Between
[Forms]![crReportOptionsFrm]![startDate] And
[Forms]![crReportOptionsFrm]![endDate]));

I get the following error:
"This expression is typed incorrectly or is too complex to be evaluated.
Try simplifying the expression by assigning parts of the expression to
variables."

The problem is with the "Between dates" part of the expression.
The error msg appears in Access 2000.
The query works fine on my own computer in Access 2002.

Thanks for your help.
Cheers,
John

 

[Allen Browne]

The message can indicate that Access is unable to figure out how to handle
the data types.

To help it:
1. If the text boxes StartDate and EndDate are unbound, set their Format
property to Short Date or similar. Access can better understand the data
type, and won't accept invalid dates.

2. Declare the parameters in your query. In query design view, choose
Parameters on the Query menu, and enter two rows into the dialog:
[Forms]![crReportOptionsFrm]![startDate] Date/Time
[Forms]![crReportOptionsFrm]![endDate] Date/Time

3. The word Date is a reserved word in VBA (for the system date), so if you
really have a field called "Date", that's begging for disaster. In this
particular context, it probably won't cause a problem though.

For more reading, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html