statement too complicated?

[lynn atkinson]

This is a repost:

I have a sub form in which I want to Display overdue
dates. The calculation is carried out in the query and the
resulting field placed in the form. This field is then
conditionally formatted to display in RED if it is due in
the next 30 days or overdue (condition: if field value is
less than Date()+30)
However, the displayed results are not correct in that
some overdue courses are not appearing in red and some
courses that are not quite due are in red.

I am using Access 2002 SP1.

This seemed to work until I put an IIF statement into the
calculated field. Has this made it too complicated?

the calculation is

calc date due: IIf([qualification duration]=0,"no refresh
req",Max((DateAdd("m",[course details]![qualification
duration],[event schedule]![eventdate]))))
..

 

[Allen Browne]

The expression is not too complicated, but Access thinks the result is text,
not a date. It is not possible to conditionally format "no fresh req" as a
date.

Even if you use Null in place of the string, Access may still get the
expression wrong. Wrap it in CVDate(), i.e.
CalcDateDue: CVDate(IIf([qualification duration]=0, Null,
DateAdd("m", [qualification duration], [eventdate])))

For more information on helping Access understand the data type of your
query fields, see:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html