#Num question

[Tony Williams]

I have a number of calculated controls on a report where the value of the
individual fields in the table is 0. I'm using this formula
=(Nz([Sum Of txtClients500],0)*Nz([txtclientstotal],0))/Nz([Sum Of
txtClientsTot],0)
Sum Of txtClients500 is a calculated control form a query being the total of
txtclients500
But I get the #Num error. Is there anything I can do to force a 0 if there
is no data in any of these controls?
Thanks
Tony

 

[Allen Browne]

There's a possibility of a division by zero error here, so handle that case
specifically, and then perform the calculation (which may result in Null),
and use Nz() on that result.

Like this:
=IIF([Sum Of txtClientsTot] = 0, 0,
Nz([Sum Of txtClients500] * [txtclientstotal] / [Sum Of txtClientsTot],
0))

If that still fails, you may need to explicitly typecast those calculate
fields in their queries. Details in:
Calculated fields misinterpreted\
at:
http://allenbrowne.com/ser-45.html

 

[Tony Williams]

As ever Allen thanks that worked like a dream. Many thanks again
Tony

There's a possibility of a division by zero error here, so handle that
case specifically, and then perform the calculation (which may result in
Null), and use Nz() on that result.

Like this:
=IIF([Sum Of txtClientsTot] = 0, 0,
Nz([Sum Of txtClients500] * [txtclientstotal] / [Sum Of txtClientsTot],
0))

If that still fails, you may need to explicitly typecast those calculate
fields in their queries. Details in:
Calculated fields misinterpreted\
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a number of calculated controls on a report where the value of the
individual fields in the table is 0. I'm using this formula
=(Nz([Sum Of txtClients500],0)*Nz([txtclientstotal],0))/Nz([Sum Of
txtClientsTot],0)
Sum Of txtClients500 is a calculated control form a query being the total
of txtclients500
But I get the #Num error. Is there anything I can do to force a 0 if
there is no data in any of these controls?