Solved

MS Access Report error

Posted on 2013-06-28
2
437 Views
Last Modified: 2013-06-28
HI

I have an Access Report that contains a sub report.  The main form has control that references a total box on the sub report.  This all works fine until the sub report contains no data and then the control that references it on the main form display #error.

How can I get around this?

Many thanks
0
Comment
Question by:kenabbott
2 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39284721
Paste this function into a standard module:

Function AvoidError(n As Variant, varReplaceWith As Variant)

10      On Error GoTo AvoidError_Error

20      AvoidError = Nz(n, varReplaceWith)

AvoidError_Exit:
30      Exit Function

AvoidError_Error:
40      AvoidError = varReplaceWith
50      Resume AvoidError_Exit

End Function


 and then change the reference in your report to:

=AvoidError(<subreport reference here>, "N/A")

Jim.
0
 

Author Comment

by:kenabbott
ID: 39284888
Many thanks - worked a treat
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

860 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