Nulls to Zero's in Totaling a Subform

Experts,

I have a subform that totals lost dollars on projects. There are many cases although where there are no lost dollars on a project. I'm trying to get the null value into a zero so when it is added to the main form I don't get the #Error. My current sum field in the subform footer to toal the field "Actual Cost" where the null occurs is:

=Nz(Sum([Actual Cost]),0)

If there is no records of dollars lost although I still get the sum field blank and not a zero, hence my main form creates the #Error on the main form.

Please help.....
NuclearOilAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Paste the following into a standard module:

Function AvoidError(n As Variant, varReplaceWith As Variant)
         
    On Error GoTo AvoidError_Error
   
    AvoidError = Nz(n, varReplaceWith)
   
AvoidError_Exit:
    Exit Function
 
AvoidError_Error:
    AvoidError = varReplaceWith
    Resume AvoidError_Exit
   
End Function


Then change your current control to:

=AvoidError(Sum([Actual Cost]),0)

 Depending on your version of Access, that may or may not work.  If it doesn't work, set the controlsource to:

=Sum([ActualCost])

 and hide the control.

 Create another text control and set it's controlsource to:

=AvoidError(Forms![<myMainFormName>]![<mySubformControlName>].Form![<myHiddenSumControlName>],0)

Latter versions of Access have an optimization built-in that if it sees a domain function (like Sum()), it won't call anything else in the expression (I think it's a bug, but that's another story) and AvoidError just doesn't get called.

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, the other way to do this is check the recordset count:

=IIF(Forms![<myMainFormName>]![<mySubformControlName>].Form.RecordsetClone.Count = 0,Sum([ActualCost]))

Jim.
Helen FeddemaCommented:
I think if you just move the Nz to surround the field, you will be OK:

=Sum(Nz([Actual Cost]),0)
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<If there is no records of dollars lost although I still get the sum field blank and not a zero, hence my main form creates the #Error on the main form.>>

 Didn't catch this at first.  If the total is on the main form, then just surround the reference to the subform control with AvoidError(<subform control reference ,0)

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NuclearOilAuthor Commented:
Jim,

I tried your last comment to surround the reference in the main form and I'm still getting the #Error
NuclearOilAuthor Commented:
Helen,

I keep getting the function has the wrong number of Argument on that code.

Using Access 2000 BTW
NuclearOilAuthor Commented:
Jim,

Your code did put me on the right path, for the module this is what I got to work.

Function AvoidError(n As Variant)
         
    On Error GoTo Trap
    AvoidError = n
    Exit Function

Trap:
    AvoidError = 0
    Resume Next
End Function
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I keep getting the function has the wrong number of Argument on that code.>>

 Not sure what's up with that; what I posted in the first comment is the new routine, which takes 2 arguments.  The 1st being the value to check and the second being the value to replace it with.  Which BTW can be something like "No data", "No Records", "N/A", etc.

Jim.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.