#ERROR Issues division

I have this report on which I do a division between an embedded sub-form and
a control on the main report. When the report is run most of the controls
will have values. However, there a few that contain the dreaded "#ERROR"
message. Sample of the formula below:

=(nz([Embed_rpt_Sum_Rejected_Qty].[Report]![txt_numRejectedQty]/[txtGross1],0))

Is there a method to eliminate this error if there is no data to divide by
or into (from either control)? If there is a method to accomplish this
desired result, how?

Thanks,....

To avoid the error, you need to avoid these situations:
- the subreport has no records (since referring to non-existent text box
gives an error);
- txtGross1 is zero (since division by zero causes error);

Try:

=IIf((([Embed_rpt_Sum_Rejected_Qty].[Report].HasData = False) OR
([txtGross1]=0)), 0,
Nz([Embed_rpt_Sum_Rejected_Qty].[Report]![txt_numRejectedQty] / [txtGross1],
0))

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

"Jay" wrote in message
...
I have this report on which I do a division between an embedded sub-form
and
a control on the main report. When the report is run most of the controls
will have values. However, there a few that contain the dreaded "#ERROR"
message. Sample of the formula below:

=(nz([Embed_rpt_Sum_Rejected_Qty].[Report]![txt_numRejectedQty]/[txtGross1],0))

Is there a method to eliminate this error if there is no data to divide by
or into (from either control)? If there is a method to accomplish this
desired result, how?

Allen:

Oooops, I'm sorry, the formula should be like so, first test the txtGross1
for null or 0, then do the same for the embedded report's control:
=[txtGross1]/IIF(([Embed_rpt_Sum_Rejected_Qty].[Report].[HasData]=False),0,(nz([Embed_rpt_Sum_Rejected_Qty].[Report]![txt_numRejectedQty]),0)))

I've got something wrong, can you take a look at this formula and let me
know the correct structure?

Thanks




"Allen Browne" wrote:

To avoid the error, you need to avoid these situations:
- the subreport has no records (since referring to non-existent text box
gives an error);
- txtGross1 is zero (since division by zero causes error);

Try:

=IIf((([Embed_rpt_Sum_Rejected_Qty].[Report].HasData = False) OR
([txtGross1]=0)), 0,
Nz([Embed_rpt_Sum_Rejected_Qty].[Report]![txt_numRejectedQty] / [txtGross1],
0))

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

"Jay" wrote in message
...
I have this report on which I do a division between an embedded sub-form
and
a control on the main report. When the report is run most of the controls
will have values. However, there a few that contain the dreaded "#ERROR"
message. Sample of the formula below:

=(nz([Embed_rpt_Sum_Rejected_Qty].[Report]![txt_numRejectedQty]/[txtGross1],0))

Is there a method to eliminate this error if there is no data to divide by
or into (from either control)? If there is a method to accomplish this
desired result, how?

Hi Jay

Will let you work through this so you benefit.

IIf() contains 3 parts:
- something to test (resolves to True or False);
- the answer to return if it's True;
- the answer if it's False.
In these case, you need to test for 2 things, so you will end up with:
=IIf((part a) OR (part b), 0, the full expression here)

Part a is to test whether the subreport has data.
Part b is to test whether the divisor is zero.

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

"Jay" wrote in message
...
Allen:

Oooops, I'm sorry, the formula should be like so, first test the txtGross1
for null or 0, then do the same for the embedded report's control:
=[txtGross1]/IIF(([Embed_rpt_Sum_Rejected_Qty].[Report].[HasData]=False),0,(nz([Embed_rpt_Sum_Rejected_Qty].[Report]![txt_numRejectedQty]),0)))

I've got something wrong, can you take a look at this formula and let me
know the correct structure?

Thanks




"Allen Browne" wrote:

To avoid the error, you need to avoid these situations:
- the subreport has no records (since referring to non-existent text box
gives an error);
- txtGross1 is zero (since division by zero causes error);

Try:

=IIf((([Embed_rpt_Sum_Rejected_Qty].[Report].HasData = False) OR
([txtGross1]=0)), 0,
Nz([Embed_rpt_Sum_Rejected_Qty].[Report]![txt_numRejectedQty] /
[txtGross1],
0))


"Jay" wrote in message
...
I have this report on which I do a division between an embedded sub-form
and
a control on the main report. When the report is run most of the
controls
will have values. However, there a few that contain the dreaded
"#ERROR"
message. Sample of the formula below:

=(nz([Embed_rpt_Sum_Rejected_Qty].[Report]![txt_numRejectedQty]/[txtGross1],0))

Is there a method to eliminate this error if there is no data to divide
by
or into (from either control)? If there is a method to accomplish this
desired result, how?