Suppress #Error result in Access reports

Posted on 2007-10-09
Last Modified: 2012-05-05
I have multiple nested reports whereby all the final sums of the individual reports roll up into fields on the report footer.  If any one report has no data an #Error shows in the roll-up sums even though the report doesn't show at all.  Either I'd like to Nz() all the fields on the sub reports so that the sums are zero or Nz() the roll-up fields so that the #Error does not show and the field can be used in calculating the grand total.  One #Error ripples through the results and I get no Grand Total because ONE field is empty or one row is empty.
Question by:jdfuller
    LVL 8

    Expert Comment

    by:Bradley Haynes
    Use this function it is from Excel but behind it all is VBA: modify for your code what is important is the [=If(IsError] part.

    LVL 77

    Accepted Solution

    Nz will not work.  It's not that the field is Null, the field does not exist, and so you get an error.

    You will have to create a series of controls in your main form (or a very long controlsource in one) in which you test whether each subreport HasData:

     =IIF([MySubRpt1].Report.HasData=True, My[SubRpt1].Report![TotalField], 0)
    You have to do this for each subreport which might be empty.

    Then add up the results.

    Author Comment

    Peter:  I think you are on it.  I will try your solution first tomorrow (PST) and get back to you asap on the points.  If you are correct, I'll have to do that for each control that is looking for a sub report field - 48ea.  I'm sure the first field will be telling enough to say whether the solution will work or not.  Copy and paste (and edit), man!  Thanks, guys.

    Author Comment

    Right on, Peter!  Thanks.  Sorry for the wait on the points. :o)

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    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…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    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…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    730 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

    17 Experts available now in Live!

    Get 1:1 Help Now