Solved

Nulls to Zero's in Totaling a Subform

Posted on 2012-03-10
8
342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 58
ID: 37705015
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 58
ID: 37705023
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
ID: 37705035
I think if you just move the Nz to surround the field, you will be OK:

=Sum(Nz([Actual Cost]),0)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 37705056
<<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
 

Author Comment

by:NuclearOil
ID: 37705099
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
ID: 37705104
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
ID: 37705159
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 58
ID: 37705354
<<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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

695 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