Solved

Nulls to Zero's in Totaling a Subform

Posted on 2012-03-10
8
338 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
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 57
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 57

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 57
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

770 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