MS access 2003 - How can i trap for "#Error on a calculated field on a form

I have a main form with the field amount remaining.
On a subform1 I list bonds $ amounts and total them in a field called CompanyBondtotal
On another subform2 I list bond deductions and in a field on that form I sum up total deductions.

In the amount remaining field on the main form I have a formula that calculates the amount remaining using the formula
=[bbonds3].[Form]![CompanyBondTotal]-[bbonddeduct].[Form]![EncomberedTotal] on the control source of that field.

Everything works fine until there are no bond deduction records to subtract from the CompanyBondTotal .

If there are no records in subform2 my amount remaining field on the main form indicates #Error. How can I get the amount remaining field to indicate zero (0) when the formula above has no value in [bbonddeduct].[Form]![EncomberedTotal] part of the formula.

I have tried the following but cannot get it to work.

=IIf(([bbonds3].[Form]![CompanyBondTotal]-[bbonddeduct].[Form]![EncomberedTotal])="#Error",0,([bbonds3].[Form]![CompanyBondTotal]-[bbonddeduct].[Form]![EncomberedTotal]))

=IIf(([bbonds3].[Form]![CompanyBondTotal]-[bbonddeduct].[Form]![EncomberedTotal])="",0,([bbonds3].[Form]![CompanyBondTotal]-[bbonddeduct].[Form]![EncomberedTotal]))

=IIf(([bbonds3].[Form]![CompanyBondTotal]-[bbonddeduct].[Form]![EncomberedTotal])=0,0,([bbonds3].[Form]![CompanyBondTotal]-[bbonddeduct].[Form]![EncomberedTotal]))

=IIf(([bbonds3].[Form]![CompanyBondTotal]-[bbonddeduct].[Form]![EncomberedTotal])=null,0,([bbonds3].[Form]![CompanyBondTotal]-[bbonddeduct].[Form]![EncomberedTotal]))

Any ideas would be greatly appreciated.

Lou
LVL 1
Lou DufresneIT Business Analyst CSM / Project ManagerAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
afaik you can't trap #error; you have to manually find the error and fix it.

>when the formula above has no value in [bbonddeduct].[Form]![EncomberedTotal] part of the formula.
Use the Nz(value, value if null) to trap for nulls, if this is your issue.

Nz([bbonddeduct].[Form]![EncomberedTotal],0) = 0 if EncomberedTotal does not have a value
0
 
harfangConnect With a Mentor Commented:
Hello Ldufresne19

Jim is right, you can't trap the #Error message display. There is a function called IsError(), but it doesn't work as expected.

There is another problem: you should not get #Error in your case. This occurs when you are referring to a control on a form without any record (not even the new record) or if you have a spelling error in your function. If both CompanyBondTotal and EncomberedTotal are defined as "=Sum(<field name>)" and are both on the subform's header or footer, they should contains a number or Null, but not #Error.

Make sure they are in the header or footer and try each alone first, e.g.:

    =[bbonddeduct].[Form]![EncomberedTotal]

Hope this  helps!
(°v°)
0
 
Lou DufresneIT Business Analyst CSM / Project ManagerAuthor Commented:
Thanks everyone. Both answers were very helpful.

Lou
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.