Predictive dialers generate profit

Menu
+ Free On-Line Tutorial

-Avoid #Error

-Customize 'Not In List' msg

-Programmatically add items to a combo box list

-Programmatically hide controls

-Security

MS Access Tools

About Us

Consulting Services




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

Question:
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)

 

Solution:

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

  

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 
orders@morewebsales.com
Office: 1-888-287-9497 (toll free)
Fax: 702-447-1401
International Callers