Solved

Nulls to Zero's in Totaling a Subform

Posted on 2012-03-10
8
336 Views
Last Modified: 2012-03-10
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.....
0
Comment
Question by:NuclearOil
  • 4
  • 3
8 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
BTW, the other way to do this is check the recordset count:

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

Jim.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
I think if you just move the Nz to surround the field, you will be OK:

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

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
<<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.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:NuclearOil
Comment Utility
Jim,

I tried your last comment to surround the reference in the main form and I'm still getting the #Error
0
 

Author Comment

by:NuclearOil
Comment Utility
Helen,

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

Using Access 2000 BTW
0
 

Author Closing Comment

by:NuclearOil
Comment Utility
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
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

763 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now