+ Free On-Line Tutorial
-Customize 'Not In List' msg
-Programmatically add items to a combo box list
-Programmatically hide controls
MS Access Tools
Tools, Add-ins, and Tutorials for Microsoft Access Developers
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?
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
'This function would only generate an error
'if the data in TheValue doesn't exist at all.
NullToZero = 0
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
2245 N. Green Valley Parkway, #212
Henderson NV 89014
Office: 1-888-287-9497 (toll free)