Solved

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

Posted on 2015-01-23
10
77 Views
Last Modified: 2015-01-23
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
0
Comment
Question by:Linda Quintana
  • 4
  • 4
  • 2
10 Comments
 
LVL 57

Expert Comment

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

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 57

Expert Comment

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

Author Comment

by:Linda Quintana
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:Linda Quintana
Comment Utility
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
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
 
LVL 57

Expert Comment

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

Jim.
0
 
LVL 1

Author Comment

by:Linda Quintana
Comment Utility
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
 
LVL 1

Author Closing Comment

by:Linda Quintana
Comment Utility
Very quick response.  It's like I had my own personal tutor.   Thank you greatly.
Linda
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

771 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

12 Experts available now in Live!

Get 1:1 Help Now