comparing dates (boolean) for conditional formatting (1 Viewer)

[bpd]

I am generating a report that tracks progress in certain qualifications (with the following fields showing):

lastname duedate %complete

I can already use conditional formatting to show the background of the "%complete" cell as green if %complete=100 (finished).

What I would like to do is generate another conditional format criteria for the %complete column for qualifications that are overdue: To do this I was trying to take the data from the "duedate" cell and compare to Date() via boolean:

If "duedate" < Date() and %complete < 100 then format cell background to RED.

But it doesn't work. I was trying to say if todays date is later in time than the due date (and the qualification isn't finished (100%)) then I want the cell to show red. (sort of like a quick stoplight chart). Perhaps I can't use the data from the previous cell (date) as a conditional format critera for a numeric cell?

Any suggestions?

 

[bpd]

follow-on question:

The above report gives me the status of everyone and their qualifications and I can look through it and see the overdue ones based upon the background color (at least I hope it will in the future).

So how do I generate the same report and only show the "overdue" records based upon the same boolean comparison?

Thanks,

Brian

 

[WayneRyan]

bpd,

Make a new field in your query:

NewField: IIf([duedate] < Date() And [%complete] < 100, True, False)

Then in the Criteria line (in the query), put True. Only those records will
show up in the query (and report).

Wayne

 

[bpd]

Wayne,

Thanks a bunch. I just used the following in the "conditional formatting" cell of the report:

[duedate] < Date() And [%complete] < 100

and it worked perfectly. Didn't have to use IIF statement. I was able to generate a report of ONLY the overdue records by using two parameters to filter the query. DueDate<Date() in the DueDate column and <100 in the %complete field. That worked. What I was trying to do was be able to see the overdue ones along with everything else.

Thanks again.

Brian