#Error and Zero Value Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Whitney - 28 Feb 2008 20:26 GMT

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.

Reply to this Message

Duane Hookom - 28 Feb 2008 20:31 GMT

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]))

Signature

Duane Hookom
Microsoft Access MVP

> 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
[quoted text clipped - 4 lines]
> Also, in the query I tried Is Not Null criteria for AVG Hold Time, but get
> an overflow error.

Reply to this Message