Predictive dialers generate profit

+ Free On-Line Tutorial

-Avoid #Error

-Customize 'Not In List' msg

-Programmatically add items to a combo box list

-Programmatically hide controls


MS Access Tools

About Us

Consulting Services

Tools, Add-ins, and Tutorials for Microsoft Access Developers 888-287-9497

How do I avoid #Error in my reports? I want to use Sum([InvoiceAmt]). InvoiceAmt is a number field. However, in some cases there is no data and the Sum function does not work. The report prints a #Error and the total is "#Error". How can I get around this?

report_error.gif (741 bytes)



I ran into this problem once when I was creating a database for a National bakery company. The problem I experience was due to the fact that I the report that was based off of a query of several tables with an Outer Joins as the relationships. An Outer Joins means that sometimes a field, like InvoiceAmt, has no value. In this case it is not a simple matter of InvoiceAmt having a Null value. Because there is no related record, there is no data perform an operation on. Any function like Sum or Avg can't resolve this dilemma and returns an "#Error".

 The way to solve this is to create a function that returns zero if it encounters this scenario. Here is how I solved it.


Public Function NullToZero( TheValue as Variant)

'This function converts Null to Zero

'It also converts Non Existing Data to Zero

On Error Goto NullToZero_Err


If ISNull(TheValue) then

NullToZero = 0


NullToZero = TheValue

End if

Exit Function



'This function would only generate an error

'if the data in TheValue doesn't exist at all.

NullToZero = 0

Exit Function

End Function


To use this in a report, set the control source of a text box to =Sum(NullToZero([InvoiceAmt]). That's all there is to it.

[Free On-Line Lessons] [ MS Access Tools ]  [Consulting] [About Us] [ Home ]

Copyright More Web Sales, LLC All Rights Reserved.
2245 N. Green Valley Parkway, #212
Henderson NV  89014
Office: 1-888-287-9497 (toll free)
Fax: 702-447-1401
International Callers