How do I get rid of #Num!

[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

 

[Ali]

Use an iif statement to check for a 0 in the fields as part of the
calculation:

 

[Tony Williams]

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

 

[Duane Hookom]

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

 

[Tony Williams]

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

 

[Duane Hookom]

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

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

 

[Tony Williams]

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

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

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

 

[Duane Hookom]

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

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

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

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