statement too complicated?

L

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]))))
..
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conditional formatting 1
Excel Excel due and overdue date colours 2
date due calculation 3
Dateadd and conditional true false statement 4
Update field daily 3
IIf Statement problem 9
query for latest course 1
#name? error 3

Top