Set zero values for results in Access 2003 Reports

[Judy Rose]

I have a report where I calculate days overdue. Problem is if the item isn't
overdue the value is negative. I did a conditional formatting so the
negative values don't show, however when I go to calculate the average days
overdue I get a negative overall value because even though masked the
negative values are there. I need to know where and how to program an Iff
statement in Access 2003 that will take any negative values and set them to
zero. (BTW I know how to do this in Crystal Reports, but apparently Access
doesn't like the way Crystal formats things).

 

[Duane Hookom]

You can use an expression like:

=IIf([DueDate]>Date(), Null, Date() - [DueDate])

Your average of all records overdue should then be:
=Avg(IIf([DueDate]>Date(), Null, Date() - [DueDate]))

The average will ignore records not overdue. Is this what you want or do you
want to figure all records?

 

[Judy Rose]

Thank you Duane, what you proposed is I think what will work. I wanted all
negative values zero'd out because they are not past due. I have to make
sure that the Null would work for what my supervisor wants, if so then you
have made my day and Thank you again.

JR

You can use an expression like:

=IIf([DueDate]>Date(), Null, Date() - [DueDate])

Your average of all records overdue should then be:
=Avg(IIf([DueDate]>Date(), Null, Date() - [DueDate]))

The average will ignore records not overdue. Is this what you want or do you
want to figure all records?


--
Duane Hookom
MS Access MVP

I have a report where I calculate days overdue. Problem is if the item
isn't
overdue the value is negative. I did a conditional formatting so the
negative values don't show, however when I go to calculate the average
days
overdue I get a negative overall value because even though masked the
negative values are there. I need to know where and how to program an Iff
statement in Access 2003 that will take any negative values and set them
to
zero. (BTW I know how to do this in Crystal Reports, but apparently Access
doesn't like the way Crystal formats things).