#Num question

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

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tony Williams" wrote in message
...
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?

As ever Allen thanks that worked like a dream. Many thanks again
Tony
"Allen Browne" wrote in message
...
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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tony Williams" wrote in message
...
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?