Trap for #Error

  • Thread starter Thread starter Alan Fisher
  • Start date Start date
A

Alan Fisher

I have a field that adds two other fields. When one or
both of the other two fields are null I get an #Error in
the block as Expected. I would like to trap for this so I
can display an alternate text box instead. I have seen it
done before but can't find it. Thanks for any help. Alan
 
What would you like to display as alternative text? You could try
=Nz(FieldA+FieldB,"alternate text")
 
If the fields are Null, you would expect the result to be Null, not #Error.

The best solution will be to understand what is causing the error. For
example, if you refer to a text box in a subreport and the subreport has no
data, you get #Error because you referred to a non-existent text box. If you
divide anything by zero, you get #Error. If the data types do not match, you
can get #Error.

You can convert nulls to zero with Nz(), but I don't believe that will solve
this problem. Post back with more information about the ControlSource of the
3 text boxes if you are still stuck.
 
I have three subreports for showing fuel tank(s) usage.
The first subreport shows the tank level at the beginning
of the reporting period, the second shows all the delivery
dates and quantities and the third shows the tank level at
the end of the reporting period. I then have a text box on
the main report that does the math to show fuel usage.
Here is the control source for that text box:


=round([rptFuelAnuualReportBeginingQty].[Report]!
[txtInitialReadingGal]-[rptFuelAnnualReportEndingQty].
[Report]![txtFinalReadingGal]+[rptFuelReportDeliveries].
[Report]![txtDeliveryQtyTotal]) & " Gallons"

This works for all tanks that have valid data. The problem
is that some tanks either had no readings or deliveries
during the reporting period and for those I get the #ERROR
in the text box. Thats fine except I would like to display
something like "No Data for Tank # 1234" instead of seeing
the error. I was thinking in the On Format event of the
report I could trap for that error, make the text box
invisible, make a label visible using an if,then,else
statement. I hope that is clear enough to allow you to
try and help me. Thanks for all your help. Alan.





=round([rptFuelAnuualReportBeginingQty].[Report]!
[txtReadingGal]-[rptFuelAnnualReportEndingQty].[Report]!
[txtReadingGal]+[rptFuelReportDeliveries].[Report]!
[txtDeliveryQtyTotal]) & " Gallons"
 
Okay, so it was the subreport one.

Test the HasData property of the report in the subreport control, like this:

=IIf([rptFuelAnuualReportBeginingQty].[Report].[HasData],
Nz([rptFuelAnuualReportBeginingQty].[Report]![txtInitialReadingGal], 0), 0)

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

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

Alan Fisher said:
I have three subreports for showing fuel tank(s) usage.
The first subreport shows the tank level at the beginning
of the reporting period, the second shows all the delivery
dates and quantities and the third shows the tank level at
the end of the reporting period. I then have a text box on
the main report that does the math to show fuel usage.
Here is the control source for that text box:


=round([rptFuelAnuualReportBeginingQty].[Report]!
[txtInitialReadingGal]-[rptFuelAnnualReportEndingQty].
[Report]![txtFinalReadingGal]+[rptFuelReportDeliveries].
[Report]![txtDeliveryQtyTotal]) & " Gallons"

This works for all tanks that have valid data. The problem
is that some tanks either had no readings or deliveries
during the reporting period and for those I get the #ERROR
in the text box. Thats fine except I would like to display
something like "No Data for Tank # 1234" instead of seeing
the error. I was thinking in the On Format event of the
report I could trap for that error, make the text box
invisible, make a label visible using an if,then,else
statement. I hope that is clear enough to allow you to
try and help me. Thanks for all your help. Alan.





=round([rptFuelAnuualReportBeginingQty].[Report]!
[txtReadingGal]-[rptFuelAnnualReportEndingQty].[Report]!
[txtReadingGal]+[rptFuelReportDeliveries].[Report]!
[txtDeliveryQtyTotal]) & " Gallons"
-----Original Message-----
If the fields are Null, you would expect the result to be Null, not #Error.

The best solution will be to understand what is causing the error. For
example, if you refer to a text box in a subreport and the subreport has no
data, you get #Error because you referred to a non- existent text box. If you
divide anything by zero, you get #Error. If the data types do not match, you
can get #Error.

You can convert nulls to zero with Nz(), but I don't believe that will solve
this problem. Post back with more information about the ControlSource of the
3 text boxes if you are still stuck.
Click to expand...
Alan
Click to expand...
 
Back
Top