How to suppress #Error in MS Access 97 Report

Posted on 2003-03-31
Medium Priority
Last Modified: 2013-11-28
I have a Microsoft Access Report which consists of several subreports some of the subreports feed off other subreports and do division calculations.  If one of the fields is null then access will display #Error message in the MS Access Report View.  Is it possible to supress this error message, or do do some logic on whether one of the other fields in the other subreports is empty?  The subreport calc is the following:

=[Text0]/([Reports]![Rep_Individual_Report_1]![Qry_Select_HistWaivers subreport].[Report]![CREATED_LST_WK]+[Reports]![Rep_Individual_Report_1]![Qry_ActiveWaiversPastWeek_UserName subreport].[Report]![Text0])

Any thoughts?


Question by:ndedich
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 8243355
The Nz() function will replace null with a value of your choice (e.g. zero or an empty string):

=nz([Text0]/([Reports]![Rep_Individual_Report_1]![Qry_Select_HistWaivers subreport].[Report]![CREATED_LST_WK]+[Reports]![Rep_Individual_Report_1]![Qry_ActiveWaiversPastWeek_UserName subreport].[Report]![Text0]),0)

Or replace the 0 at the end with "" for an empty string.  Does that fix it?

Accepted Solution

ornicar earned 1000 total points
ID: 8243863
You must use the IsError() function, along with an iif() statement:

=iif(IsError(=[Text0]/([Reports]![Rep_Individual_Report_1]![Qry_Select_HistWaivers subreport].[Report]![CREATED_LST_WK]+[Reports]![Rep_Individual_Report_1]![Qry_ActiveWaiversPastWeek_UserName subreport].[Report]![Text0]),"",=[Text0]/([Reports]![Rep_Individual_Report_1]![Qry_Select_HistWaivers subreport].[Report]![CREATED_LST_WK]+[Reports]![Rep_Individual_Report_1]![Qry_ActiveWaiversPastWeek_UserName subreport].[Report]![Text0])


Author Comment

ID: 8249850
Thanks Guys,

I haven't been able to test these strategies, but I have been able to translate the problem into pure Access SQL logic, rather than putting the logic into the Report Text Field.  Unfortunately the text fields I was referring to were subreports in the Main Report and so behaved differently in the report than individually as Sub Report.

I will try both these posts shortly and will grade the correct answers.

Author Comment

ID: 8493571
I believe this answer is most effective in solving this problem, I ended up using an iif function in the SQL rather than in the form, thankyou for this answer ornicar and Amigo I will try your answer latr,

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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 …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 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