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
Solved

Nulls to Zero's in Totaling a Subform

Posted on 2012-03-10
8
339 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

809 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