Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2015-01-23
10
Medium Priority
?
91 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 59
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 59
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 59

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 59
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

564 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