asked on 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.....
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.....
Microsoft Access
Last Comment
Jim Dettman (EE MVE)
BTW, the other way to do this is check the recordset count:
=IIF(Forms![<myMainFormNam
Jim.
=IIF(Forms![<myMainFormNam
Jim.
I think if you just move the Nz to surround the field, you will be OK:
=Sum(Nz([Actual Cost]),0)
=Sum(Nz([Actual Cost]),0)
ASKER CERTIFIED SOLUTION
THIS SOLUTION 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.
ASKER
Jim,
I tried your last comment to surround the reference in the main form and I'm still getting the #Error
I tried your last comment to surround the reference in the main form and I'm still getting the #Error
ASKER
Helen,
I keep getting the function has the wrong number of Argument on that code.
Using Access 2000 BTW
I keep getting the function has the wrong number of Argument on that code.
Using Access 2000 BTW
ASKER
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
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
<<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.
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.
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![<myMain
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.