Solved

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

Posted on 2015-01-23
10
81 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
ID: 40566433
<<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
ID: 40566449
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
ID: 40566481
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
ID: 40566732
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
ID: 40566819
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:Linda Quintana
ID: 40567019
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
ID: 40567048
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
ID: 40567052
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
ID: 40567159
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
ID: 40567164
Very quick response.  It's like I had my own personal tutor.   Thank you greatly.
Linda
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

932 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