Access 2013 #safe! error on report -- subreports may have not data

I am using Access 2013
I am trying to create a total on a report using the totals generated in subreports.
Here is the code that I am using -- it returns a value of   #safe!

=(Val(Nz([rptAnnualBillingforCSS subreport].[Report]![AccessTotalsBILLDISTCOST],0))+Val(Nz([rptAnnualBillingforDistribution subreport].[Report]![AccessTotalsBILLCSSCOST],0))+Val(Nz([rptAnnualBillingforVIP subreport].[Report]![AccessTotalsStandardCost],0)))

Am I missing something?
Thanks.
Linda
LVL 1
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
Try it like this:

=AvoidError([rptAnnualBillingforCSS subreport].[Report]![AccessTotalsBILLCSSCOST],0)+AvoidError([rptAnnualBillingforDistribution subreport].[Report]![AccessTotalsBILLDISTCOST],0)+AvoidError([rptAnnualBillingforVIP subreport].[Report]![AccessTotalsStandardCost],0)

Let me know...

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Am I missing something?>>

  Yes.   A reference to a subreport (or form) with no data results in a #Error, not a null.  Paste the function below into a standard module, then wrap your expressions with it.

Jim.

Function AvoidError(n As Variant, varReplaceWith As Variant)

10      On Error GoTo AvoidError_Error

20      If IsError (n) then
            AvoidError = varReplaceWith
         Else
            AvoidError = Nz(n, varReplaceWith)
         End If

AvoidError_Exit:
30      Exit Function

AvoidError_Error:
40      AvoidError = varReplaceWith
50      Resume AvoidError_Exit

End Function

Open in new window

0
 
Jeffrey CoachmanMIS LiasonCommented:
There are a couple of ways to deal with his,

Then main issue is that when a subreport does not have data it is not really "NULL", ...it just does not exist.
One fix is to use iserror()
IIF(Iserror(YourExpression),0,YourExpression)
But sometimes these expressions can get long and difficult to troubleshoot.

What I sometimes do is use an aggregate function to get the totals
Dsum("YourCostField", "YourCostTable")
If I need grouping or filters, ...then I add the Where criteria:
Dsum("YourCostField", "YourCostTable","YourEmployeeID=" & txtYourEmployeeID)

Hope this helps,...
Lets see what other experts contribute...

JeffCoachman
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
I would add that there are a few cases where IsError() doesn't work.   AvoidError() works in the majority of cases and in some cases where IsError() does not, but even so, there are still some where even it will not.

If you find that it doesn't, then you need to actually check for records in the subreport (use the HasData property)  or use another method like Jeff pointed out (using Dsum()).

Jim.
0
 
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
Jim,    

I am going to try your solution first.    I have created the module, but you say that I need to 'wrap my expression' with this.    How do I do that?   I'm getting more familiar with VBA and adding code to my databases, but I'm not sure what I would need to do to activate this module in my expressions.

Thank you for your continued help.

Linda
0
 
Jeffrey CoachmanMIS LiasonCommented:
Yes, go with Jim's solution...
Jim made me aware of this technique a while ago.
I might go with IsError for simple stuff, but what Jim posted is much more elegant for nested objects.

Jeff
0
 
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
Ok.    
So here is my code now and I'm still getting the same #size! error.    Any thoughts?

=AvoidError((Nz([rptAnnualBillingforCSS subreport].[Report]![AccessTotalsBILLCSSCOST],0))+(Nz([rptAnnualBillingforDistribution subreport].[Report]![AccessTotalsBILLDISTCOST],0))+(Nz([rptAnnualBillingforVIP subreport].[Report]![AccessTotalsStandardCost],0)),0)

Linda
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
and BTW, if you display those fields, they do work right when the report has data, and they are numeric, correct?

Jim.
0
 
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
Jim,

Thank you so very much for your quick and efficient help.    Yes, my reports were working independently (meaning, the totals were showing correctly) and they are defined as numeric fields.

With your help and expertise, the report total is printing correctly.  I appreciate your wisdom.

Regards,
Linda
0
 
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
Very quick response.  It's like I had my own personal tutor.   Thank you greatly.
Linda
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.