Re: Using the IsError Function

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)

"Michael" <Michael@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D7E48572-7409-47C3-95FE-DF750A52D257@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Null values and calculations
    ... You can use the IIf() expression in the Control Source of a text box. ... another cause of #Error is calculated expressions when the report has no records. ... >> messages where this formaul was being used in a calculations. ...
    (microsoft.public.access.reports)
  • Re: Display control dependant on contents of another control
    ... Darhl ... > I've gotta learn that IIF better. ... >> Simplest thing is to add a text box to the report, and set its Control ...
    (microsoft.public.access.forms)
  • Re: How do I use the IIF expression
    ... ControlSource property of an unbound textbox control on a form (or a ... the If *statement* is used in VBA code, behind a form or report, to ... if you're wanting to display or hide a control ... if you prefer to use the IIf() function in a textbox control's ...
    (microsoft.public.access.gettingstarted)
  • IIf function not producing desired results
    ... their values from the query that the report is based upon. ... The "Games Played" control receives it's value from three IIf ...
    (microsoft.public.access.reports)
  • Re: Create tables from large report
    ... Your post is very confusing. ... where is the report getting the data from? ... When I run a query to control the form and submit the ... data, the form comes up blank, with all the controls, label, etc. missing. ...
    (microsoft.public.access.tablesdbdesign)