• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 787
  • Last Modified:

Suppress #Error result in Access reports

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.
0
jdfuller
Asked:
jdfuller
  • 2
1 Solution
 
Bradley HaynesCommented:
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.

=If(IsError(VLOOKUP(B29,exchange,2)),"",VLOOKUP(B29,exchange,2))
0
 
peter57rCommented:
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.
0
 
jdfullerAuthor Commented:
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.
0
 
jdfullerAuthor Commented:
Right on, Peter!  Thanks.  Sorry for the wait on the points. :o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now