[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Suppress #Error result in Access reports

Posted on 2007-10-09
Medium Priority
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
  • 2

Expert Comment

by:Bradley Haynes
ID: 20045500
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

peter57r earned 2000 total points
ID: 20045592
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

ID: 20046318
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

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

834 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