#Error and Zero Value Help

[Whitney]

I have a query with the following equation AVG Hold Time: [Total Hold
Time]/[Total ACD]. Some fields show #error or a zero value (0:00:00). On a
report footer I would like to average the AVG Hold Time by month or even
better take the sum of Total ACD and the sum of Total Hold Time on the report
and divide those. Either way I keep getting an Overflow error.

Also, in the query I tried Is Not Null criteria for AVG Hold Time, but get
an overflow error.

 

[Duane Hookom]

For a single record (detail section) you would normally use:
=IIf(Nz([Total ACD],0) =0, Null, [Total Hold Time]/[Total ACD])
In a group/report header or footer
=IIf(Nz(Sum([Total ACD]),0) =0, Null, Sum([Total Hold Time])/Sum([Total ACD]))