[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-05-09
3
Medium Priority
?
268 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:Lou Dufresne
3 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 total points
ID: 16639516
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
 
LVL 58

Assisted Solution

by:harfang
harfang earned 1000 total points
ID: 16680296
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
 
LVL 1

Author Comment

by:Lou Dufresne
ID: 16687153
Thanks everyone. Both answers were very helpful.

Lou
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question