Access - #Error when Control Source formula has a blank subform value

wlwebb
wlwebb used Ask the Experts™
on
I have a tabbed form that on one of the tabs I have 4 subforms.  On the tabbed form I have a textbox to display for the user the % Complete that a Job is by comparing the Total Actual Cost vs the Total Estimated Cost.

I am taking a textbox totals field of one subform and using it to compute a % of completion from a textbox totals field from another subform.

When one of the subforms is blank (ie: no costs have been input on a job yet) then I get #Error.

I have tried IIF() statements and Error$ to get it to display as 0% when it is computing #Error.  I can not get the syntax correct.

Below is my formula.  What do I change to get it to display as 0% if it is in #Error?
=Nz([sfrmJobSummaryItemTotalsOnly].[Form]![txtTotalAllItems]/[sfrmJCJobEstimateEstimatedCosts].[Form]![Item Cost Estimate],0)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
try this one


=IIF(nz([sfrmJobSummaryItemTotalsOnly].[Form]![txtTotalAllItems],0)=0,0,[sfrmJobSummaryItemTotalsOnly].[Form]![txtTotalAllItems]/nz([sfrmJCJobEstimateEstimatedCosts].[Form]![Item Cost Estimate],0)
Top Expert 2011

Commented:
I use the IsError()

Try:

=IIf(IsError([sfrmJobSummaryItemTotalsOnly].[Form]![txtTotalAllItems])=True,0,Nz([sfrmJobSummaryItemTotalsOnly].[Form]![txtTotalAllItems],0)/Nz([sfrmJCJobEstimateEstimatedCosts].[Form]![Item Cost Estimate],0))

Open in new window


The issue will be if [sfrmJCJobEstimateEstimatedCosts].[Form]![Item Cost Estimate] is zero (0).

Top Expert 2011
Commented:
This tests for both:

=IIf(IsError([sfrmJobSummaryItemTotalsOnly].[Form]![txtTotalAllItems])=True or IsError([sfrmJCJobEstimateEstimatedCosts].[Form]![Item Cost Estimate]) = True,0,Nz([sfrmJobSummaryItemTotalsOnly].[Form]![txtTotalAllItems],0)/Nz([sfrmJCJobEstimateEstimatedCosts].[Form]![Item Cost Estimate],0))

Open in new window


You really need to test to make sure you are not dividing by zero.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Also check this link out about #Error if you have no records in Subform:

http://access.mvps.org/access/forms/frm0022.htm

HTH,
Daniel

Author

Commented:
Worked like a charm.  Thanks to all that replied!!
Top Expert 2016

Commented:
my mistake should have posted, oh well ;-(

=IIF(nz([sfrmJCJobEstimateEstimatedCosts].[Form]![Item Cost Estimate],0)=0,0,[sfrmJobSummaryItemTotalsOnly].[Form]![txtTotalAllItems]/[sfrmJCJobEstimateEstimatedCosts].[Form]![Item Cost Estimate])

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial