Using the IsError Function

[Michael]

Hello All.

I am designing some reports for my boss, and he would like them to be a
little cleaner. For example, one report has a list of items and then a
sumation of the cost (=sum([Amount]). If there happens to be no items
present, the report shows "#Error" because you cannot sum a null value.

I was able to resolve this by using:
=IIf(IsNull([Amount]), "No Data", sum([Amount])

However, I would like to have a more comprehensive approach (to use on other
errors that may occur). Something like the following:

=IIf(IsError(<exp1>), "No Data", <exp1>)

i.e.
=IIf(IsError(sum([Amount]), "No Data", sum([Amount]))

This formula still gives me the "#Error" value. I believe this is due to the
IsError function examining the expression, not the returned value of the
expression.

Is there anyway to force access to evaluate the expression first, to make
sure it reviews the value not the expression? For example,

=IIf(IsError(evaluate(sum([Amount]))), "No Data" ....... or somthing similar?

Any assistance you could provide would be greatly appreciated.

Thanks!

Michael

 

[Duane Hookom]

The only way I get get the #Error to appear is if there are no records in the
recordsource. Having records with Null values doesn't produce #Error.

If you might not return records, the general solution is to use:
=IIf(HasData,Sum([Amount]), Null)
You can set the Format property of the text box to display "No Data" if the
text box is Null.

 

[George Nicholson]

From Help entry for Iif:

"IIf always evaluates both truepart and falsepart, even though it returns
only one of them. Because of this, you should watch for undesirable side
effects. For example, if evaluating falsepart results in a division by zero
error, an error occurs even if expr is True."

Try something like:

IIf(Sum(nz([Amount],0)) = 0, "No Data", Sum(nz([Amount],0))

 

[Guest]

1) You can sum a null value. That is not exactly what is giving you #Error.

On a report, [Amount] may refer to a control. A missing control
may give you #Error. A control may be missing if a section is missing:
a section may be missing if there is no data. I normally used code
to hide the summary values if data was missing. The way you have
done it seems to work satisfactorily.

2) You can't use IsError in a query, because IsError reports a
property of a VBA/OLE Variant type values. Queries have values
which are not VBA/OLE Variants, so they can never be IsError.
Even if you have an expression in a query, it is never copied to a
variant, so the VBA function IsError never returns a meaningful
result. If you get a #Error in a query, there is nothing you can
do to hide or mask it. You always have to create an expression
that avoids the #Error.

3) The VBA IIF is slightly different from the Jet SQL IIF.
The VBA IIF always evaluates both branches of the IIF statement.
The Jet SQL IIF only ever evaluates one branch of the IIF.

(david)