How do I get rid of #Num!

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a report with a number of calculated controls. Here is an example
=([Sum Of txtClients0]*[txtclientstotal])/[Sum Of txtClientsTot]

However if any of the data in the fields that these controls are based on
are 0 then I'm getting #Num! How can I change my calculation to show a 0 in
place of the #Num!

Thanks
Tony
 
Sorry Ali I'm not sure I understand what you mean. I can understand the IIF
statements but how would I check for 0 in every value in my example
calculation?
Thanks Tony
Ali said:
Use an iif statement to check for a 0 in the fields as part of the
calculation:

Tony Williams said:
I have a report with a number of calculated controls. Here is an example
=([Sum Of txtClients0]*[txtclientstotal])/[Sum Of txtClientsTot]

However if any of the data in the fields that these controls are based on
are 0 then I'm getting #Num! How can I change my calculation to show a 0
in
place of the #Num!

Thanks
Tony
Click to expand...
Click to expand...
 
Zero values in your expression should not cause #Num errors. If [Sum Of
txtClientsTot] is 0 then you should get a divide by 0 type error.

Are any of these values coming from totals on subreports?

--
Duane Hookom
MS Access MVP
--

Tony Williams said:
Sorry Ali I'm not sure I understand what you mean. I can understand the
IIF
statements but how would I check for 0 in every value in my example
calculation?
Thanks Tony
Ali said:
Use an iif statement to check for a 0 in the fields as part of the
calculation:

Tony Williams said:
I have a report with a number of calculated controls. Here is an example
=([Sum Of txtClients0]*[txtclientstotal])/[Sum Of txtClientsTot]

However if any of the data in the fields that these controls are based on
are 0 then I'm getting #Num! How can I change my calculation to show a
0
in
place of the #Num!

Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
Hello again Duane!
Yes they are coming from subreports. If all the fields in the table have
data then the report works OK, it's when some of the fields in the table
have 0's that I get the #Num! message in the subreport where the calculated
control is.
Tony
Duane Hookom said:
Zero values in your expression should not cause #Num errors. If [Sum Of
txtClientsTot] is 0 then you should get a divide by 0 type error.

Are any of these values coming from totals on subreports?

--
Duane Hookom
MS Access MVP
--

Tony Williams said:
Sorry Ali I'm not sure I understand what you mean. I can understand the
IIF
statements but how would I check for 0 in every value in my example
calculation?
Thanks Tony
Ali said:
Use an iif statement to check for a 0 in the fields as part of the
calculation:

I have a report with a number of calculated controls. Here is an example
=([Sum Of txtClients0]*[txtclientstotal])/[Sum Of txtClientsTot]

However if any of the data in the fields that these controls are
Click to expand...
based
on
are 0 then I'm getting #Num! How can I change my calculation to show a
0
in
place of the #Num!

Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
There is a huge difference between no records in a subreport and a 0 value.
You can use something like:
=IIf([subreport].Report.HasData, subreport.Report.txtTotal,0)
If the subreport control named "subreport" returns any records, you will see
the value of txtTotal. If there are no records in the subreport, then you
will return 0.

Remember to also check for the division by 0.

--
Duane Hookom
MS Access MVP
--

Tony Williams said:
Hello again Duane!
Yes they are coming from subreports. If all the fields in the table have
data then the report works OK, it's when some of the fields in the table
have 0's that I get the #Num! message in the subreport where the
calculated
control is.
Tony
Duane Hookom said:
Zero values in your expression should not cause #Num errors. If [Sum Of
txtClientsTot] is 0 then you should get a divide by 0 type error.

Are any of these values coming from totals on subreports?

--
Duane Hookom
MS Access MVP
--

Tony Williams said:
Sorry Ali I'm not sure I understand what you mean. I can understand the
IIF
statements but how would I check for 0 in every value in my example
calculation?
Thanks Tony
Use an iif statement to check for a 0 in the fields as part of the
calculation:

I have a report with a number of calculated controls. Here is an example
=([Sum Of txtClients0]*[txtclientstotal])/[Sum Of txtClientsTot]

However if any of the data in the fields that these controls are based
on
are 0 then I'm getting #Num! How can I change my calculation to show a
0
in
place of the #Num!

Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
Thanks Duane is it possible to use an IIf expression like that on the OnOpen
property so that if there are no records in any of the controls on the sub
report I get the control labels but all 0's. The fields that the controls
are based on are Integer and have a default value of 0. Also I suspect there
will be some dividing by 0 in there too.
Tony
Duane Hookom said:
There is a huge difference between no records in a subreport and a 0 value.
You can use something like:
=IIf([subreport].Report.HasData, subreport.Report.txtTotal,0)
If the subreport control named "subreport" returns any records, you will see
the value of txtTotal. If there are no records in the subreport, then you
will return 0.

Remember to also check for the division by 0.

--
Duane Hookom
MS Access MVP
--

Tony Williams said:
Hello again Duane!
Yes they are coming from subreports. If all the fields in the table have
data then the report works OK, it's when some of the fields in the table
have 0's that I get the #Num! message in the subreport where the
calculated
control is.
Tony
Duane Hookom said:
Zero values in your expression should not cause #Num errors. If [Sum Of
txtClientsTot] is 0 then you should get a divide by 0 type error.

Are any of these values coming from totals on subreports?

--
Duane Hookom
MS Access MVP
--

Sorry Ali I'm not sure I understand what you mean. I can understand the
IIF
statements but how would I check for 0 in every value in my example
calculation?
Thanks Tony
Use an iif statement to check for a 0 in the fields as part of the
calculation:

I have a report with a number of calculated controls. Here is an example
=([Sum Of txtClients0]*[txtclientstotal])/[Sum Of txtClientsTot]

However if any of the data in the fields that these controls are based
on
are 0 then I'm getting #Num! How can I change my calculation to
Click to expand...
show
a
0
in
place of the #Num!

Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
If a subreport returns no records, it will not display in the main report.
You have to figure out how to work around this.

--
Duane Hookom
MS Access MVP


Tony Williams said:
Thanks Duane is it possible to use an IIf expression like that on the
OnOpen
property so that if there are no records in any of the controls on the sub
report I get the control labels but all 0's. The fields that the controls
are based on are Integer and have a default value of 0. Also I suspect
there
will be some dividing by 0 in there too.
Tony
Duane Hookom said:
There is a huge difference between no records in a subreport and a 0 value.
You can use something like:
=IIf([subreport].Report.HasData, subreport.Report.txtTotal,0)
If the subreport control named "subreport" returns any records, you will see
the value of txtTotal. If there are no records in the subreport, then you
will return 0.

Remember to also check for the division by 0.

--
Duane Hookom
MS Access MVP
--

Tony Williams said:
Hello again Duane!
Yes they are coming from subreports. If all the fields in the table
have
data then the report works OK, it's when some of the fields in the
table
have 0's that I get the #Num! message in the subreport where the
calculated
control is.
Tony
Zero values in your expression should not cause #Num errors. If [Sum
Of
txtClientsTot] is 0 then you should get a divide by 0 type error.

Are any of these values coming from totals on subreports?

--
Duane Hookom
MS Access MVP
--

Sorry Ali I'm not sure I understand what you mean. I can understand the
IIF
statements but how would I check for 0 in every value in my example
calculation?
Thanks Tony
Use an iif statement to check for a 0 in the fields as part of the
calculation:

I have a report with a number of calculated controls. Here is an
example
=([Sum Of txtClients0]*[txtclientstotal])/[Sum Of txtClientsTot]

However if any of the data in the fields that these controls are
based
on
are 0 then I'm getting #Num! How can I change my calculation to show
a
0
in
place of the #Num!

Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
Back
Top