Access - #Error when Control Source formula has a blank subform value
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?
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)
Last Comment
I use the IsError()
Try:
The issue will be if [sfrmJCJobEstimateEstimate
Try:
=IIf(IsError([sfrmJobSummaryItemTotalsOnly].[Form]![txtTotalAllItems])=True,0,Nz([sfrmJobSummaryItemTotalsOnly].[Form]![txtTotalAllItems],0)/Nz([sfrmJCJobEstimateEstimatedCosts].[Form]![Item Cost Estimate],0))
The issue will be if [sfrmJCJobEstimateEstimate
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Also check this link out about #Error if you have no records in Subform:
http://access.mvps.org/acc
HTH,
Daniel
http://access.mvps.org/acc
HTH,
Daniel
ASKER
Worked like a charm. Thanks to all that replied!!
my mistake should have posted, oh well ;-(
=IIF(nz([sfrmJCJobEstimate
=IIF(nz([sfrmJCJobEstimate
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
=IIF(nz([sfrmJobSummaryIte