Expression is too complex to be evaluated - Date Problem, Please help. :)

Luke Burden

unread,

May 3, 2002, 11:51:44 PM5/3/02

to

Greetings!

I hope someone can help me with my date problem.

I have a Query 'ReceiptsQuery' which has a field 'Date' - being a simple
dd/mm/yy format.
This query has criteria in the Date field to find only those records :

Between Forms!Accountancy.Date1 AND Forms!Accountancy.Date2

I have a list in the form Accountancy, List2 - which is based on the
ReceiptsQuery.

The idea is that you can edit the two dates, and the appropriate records
between those dates will be displayed.

My problem is this - When I open the form Accountancy, I get this message...

*****************************
This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)
*****************************

Can anyone tell me whats going wrong? When I substitute in literal dates
(#3/5/99# and #3/5/02# for example) - it works fine. It's something to do
with getting the dates from the form....?

Any help will be much appreciated. Thanks. :)

Stewart

unread,

May 4, 2002, 1:08:53 AM5/4/02

to

Try the following in the [Date] field of the query grid.

Between [Which Date From ?] And [Which Date To ?]

And don't tie it to a form, just enter the above in the query grid. And
Change the field name of [Date] as it's not recommended to use reserve words
as field names. Change to it [ReceiptDate] or something that is related to
the records.

Good luck,

Stewart

"Luke Burden" <bur...@pacific.net.au> wrote in message
news:rTIA8.1208$06.1...@nasal.pacific.net.au...

Clive Bolton

unread,

May 4, 2002, 1:24:33 AM5/4/02

to

The problem is that SQL requires a date string to be in US date format and
delimited as a date. Your form references will be delivering it in
dd/mm/yyyy format with no indication that this text is a date.

Try:

Between Format(Forms!Accountancy!Date1,"\#mm\/dd\/yyyy\#) AND
Format(Forms!Accountancy!Date2,"\#mm\/dd\/yyyy\#)

or just within Microsoft, the faster:

Between CDate(Forms!Accountancy!Date1) AND CDate(Forms!Accountancy!Date2)

Clive

"Luke Burden" <bur...@pacific.net.au> wrote in message
news:rTIA8.1208$06.1...@nasal.pacific.net.au...

Simon Mainey

unread,

May 4, 2002, 2:13:42 AM5/4/02

to

2 things:

First - DONT use reserved names. 'Date' is a reserved name - that is,
it is earmarked for use by Access etc.

So when you are saying WHERE Date > x etc it is evaluating Date as
"today", not as your field value.

Secondly - with Jet - always use USA date format.

Eg: = "#" & Format([MyDate], "mm-dd-yy") & "#"

Hope that helps you :)

Simon

Luke Burden

unread,

May 4, 2002, 3:46:19 AM5/4/02

to

THanks For the replies guys..

I tried to use the CDate function - I also renamed the Date field to EDate,
and Rdate for Expense date and Receipt date respectively.

I am still getting the same error message. :) I'm pretty stumped...

Any other ideas guys?

"Luke Burden" <bur...@pacific.net.au> wrote in message
news:rTIA8.1208$06.1...@nasal.pacific.net.au...

Clive Bolton

unread,

May 4, 2002, 6:43:54 AM5/4/02

to

Did you notice that I edited the reference:

Forms!Accountancy!Date1

You used a Period between Accountancy and Date1, and that indicates a
tablename/fieldname to SQL.

Clive

"Luke Burden" <bur...@pacific.net.au> wrote in message

news:njMA8.1210$06.1...@nasal.pacific.net.au...