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
  • 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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

569 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