Boolean comparison of dates for reports or queries? (1 Viewer)

[bpd]

I posted the following the other day in the Reports section, but I think it is a more general question. I had intended it for use as conditional formatting for a report; however, I guess I could also use it as a parameter in a query and have the report draw that data.

Help.

--------------------------------------------------------------------------

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?

 

[bjackson]

Are You talking about excel(Cells ?) or access

In access you can only use conditional formatting based on the values of that field.You cant refer to data that resides in other fields for that record.

if you want to use several criteria on a report then use the on print event for the section that holds the data and set your criteria based on the values of each control on the report that holds the data(usually text boxes)

eg.

if me.duedate < date() and me.percent <100 then
me.controlname.backcolor=vbred
elseif me.duedate = date() and me.percent <100 then
me.controlname.backcolor=vbblue
elseif me.duedate > date() and me.percent =100 then
me.controlname.backcolor=vbWhite
end if

Of course you will need to make sure you use the right names for the controls
on your report

regards
Bjackson