Grand total and Average

[Guest]

Hello,

I have created a report, based on a query with totals for fields such as
rent, utilities, sales. I created calculated field on the query that will
determine the number of months remaining on the lease (this is a lease
management database).

I've added to the report footer the grand totals and averages for rent,
utilities, sales, etc. The problem I have is with the number of months
remaining on the lease. There are several that contain negative numbers
which of course skews the grand total and averages of that field. How can I
exclude the negative numbers, or make them 0 in the calculation?

Thanks in advance.

 

[Ken Snell \(MVP\)]

Use an expression similar to this for the "number of months remaining"
calculated field:

NumMonthsRemain: IIf(Expr<0,0,Expr)

where Expr is the expression that you're using now to calculate the number
of months remaining.

 

[Guest]

Thank you Ken, that worked perfectly!

Use an expression similar to this for the "number of months remaining"
calculated field:

NumMonthsRemain: IIf(Expr<0,0,Expr)

where Expr is the expression that you're using now to calculate the number
of months remaining.