?
Solved

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

Posted on 2015-01-23
10
Medium Priority
?
89 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
[X]
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
  • 4
  • 4
  • 2
10 Comments
 
LVL 58
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 58
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
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 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
 
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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 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 58
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

762 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