AvoidError()

[Guest]

I have an Access 97 database that I have converted to Access 2000 and the
AvoidError function doesn't work now. I have a report called Invoices. The
report has a subreport for service fees that some vendors will pay, others
will not so sometimes the subreport is empty. The Total on the invoice is
calculated using a total on the service fees report. I used the avoiderror
function in 97 and it worked fine. In 2000 I'm getting the #Error when the
subreport is empty.

 

[Brian]

I have an Access 97 database that I have converted to Access 2000 and the
AvoidError function doesn't work now. I have a report called Invoices. The
report has a subreport for service fees that some vendors will pay, others
will not so sometimes the subreport is empty. The Total on the invoice is
calculated using a total on the service fees report. I used the avoiderror
function in 97 and it worked fine. In 2000 I'm getting the #Error when the
subreport is empty.

To the best of my knowledge there is no function named "AvoidError" in any
version of Access. It must be a function that was written by whoever
developed your database, so to get help with it you are going to have to
find the VBA code for the function and post it.

 

[Guest]

To the best of my knowledge there is no function named "AvoidError" in any
version of Access. It must be a function that was written by whoever
developed your database, so to get help with it you are going to have to
find the VBA code for the function and post it.


Hi Brian,

I developed the database and found the AvoidError function on the internet
and have been using it in a lot of 97 programs for a long time. Here is the
code -
Function AvoidErrorOld(n As Variant)

On Error GoTo Trap
AvoidError = n
Exit Function

Trap:
AvoidError = 0
Exit Function
Resume Next
End Function

 

[Douglas J. Steele]

I developed the database and found the AvoidError function on the internet
and have been using it in a lot of 97 programs for a long time. Here is the
code -
Function AvoidErrorOld(n As Variant)

On Error GoTo Trap
AvoidError = n
Exit Function

Trap:
AvoidError = 0
Exit Function
Resume Next
End Function

I'd get rid of that 2nd Exit Function (after the Trap label), as that
prevents the Error object from being reset, but I don't think that's
necessarily what's causing the problem.

What exactly are you passing to that function (which, by the way, is
misnamed in what you pasted)?

I would think that you could put logic into the subreport's On No Data event
(Report_NoData) to stop problems from arising.

 

[Guest]

I'd get rid of that 2nd Exit Function (after the Trap label), as that
prevents the Error object from being reset, but I don't think that's
necessarily what's causing the problem.

What exactly are you passing to that function (which, by the way, is
misnamed in what you pasted)?

I would think that you could put logic into the subreport's On No Data event
(Report_NoData) to stop problems from arising.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Sorry about that. I renamed it when I was trying to find a solution. I tried using the subreport's no data event but it doesnt seem to ever fire. I put a msg box on the event and paged through the report. I never got the msgbox. To tell you the truth I dont really have any idea how this works or why (now that's embarassing!!) but it does. I have found that sometimes NZ works and sometimes it doesn't. Usually when NZ isnt working for me this avoiderror does.

Textbox on main report is set to this:
txtFlatFees=avoiderror([FeesSubreport].[Report]![txtTotalFees]) When there
is nothing on the subreport then I get #error in the box and all following
boxes that need it for calculations. I surely appreciate any input because I
am clueless. I thought the no data event would help but now I'm stuck. How do
you handle an empty subreport that's only empty on some pages??

 

[Douglas J. Steele]

tried using the subreport's no data event but it doesnt seem to ever fire. I
put a msg box on the event and paged through the report. I never got the
msgbox. To tell you the truth I dont really have any idea how this works or
why (now that's embarassing!!) but it does. I have found that sometimes NZ
works and sometimes it doesn't. Usually when NZ isnt working for me this
avoiderror does.

Textbox on main report is set to this:
txtFlatFees=avoiderror([FeesSubreport].[Report]![txtTotalFees]) When there
is nothing on the subreport then I get #error in the box and all following
boxes that need it for calculations. I surely appreciate any input because I
am clueless. I thought the no data event would help but now I'm stuck. How do
you handle an empty subreport that's only empty on some pages??

VBA has a built-in IsError function. You should be able to use:

txtFlatFees=IIf(IsError([FeesSubreport].[Report]![txtTotalFees]), 0,
[FeesSubreport].[Report]![txtTotalFees])

 

[Guest]

tried using the subreport's no data event but it doesnt seem to ever fire. I
put a msg box on the event and paged through the report. I never got the
msgbox. To tell you the truth I dont really have any idea how this works or
why (now that's embarassing!!) but it does. I have found that sometimes NZ
works and sometimes it doesn't. Usually when NZ isnt working for me this
avoiderror does.

Textbox on main report is set to this:
txtFlatFees=avoiderror([FeesSubreport].[Report]![txtTotalFees]) When there
is nothing on the subreport then I get #error in the box and all following
boxes that need it for calculations. I surely appreciate any input because I
am clueless. I thought the no data event would help but now I'm stuck. How do
you handle an empty subreport that's only empty on some pages??

VBA has a built-in IsError function. You should be able to use:

txtFlatFees=IIf(IsError([FeesSubreport].[Report]![txtTotalFees]), 0,
[FeesSubreport].[Report]![txtTotalFees])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you a thousand times over!! That worked perfectly! I have to convert all of my databases and used that AvoidError a lot. You have saved me a ton of grief! Happy Holidays