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

Else

NullToZero = TheValue

End if

Exit Function

NullToZero_Err:

'This function would only generate an error

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

NullToZero = 0

Exit Function

End Function