iif is error

[Brian]

Ok, I'm suffering from cranial imcompetance at the moment,
I am trying to write an iif statement on a report that will generate one
value if a field has a value, and another if it has an error. In excel there
is an "if err" function (or something like that) but I cannot get it to
work.
Something like:

iif([balDue] isErr,[bidPrice],[bidPrice]-[SunOfpayments])

Thanks

 

[Guest]

The VBA function is IsError(). so the syntax would be:

=IIf(IsError([balDue]),[bidPrice],[bidPrice]-[SunOfpayments])

However I'd be doubtful that this would work if this is the ControlSource of
the balDue control itself. For one thing this would be a circular reference,
and if it’s the [bidPrice]-[SunOfpayments] expression which is the cause of
the error the IIf function requires both the True and False return values to
be capable of evaluation without error.

If you can't get the expression to work as the ControlSource of a control a
better approach might be to assign a value to the control in code in the
section's Format or Print event as this enables you to handle any error in
code, or to avoid an error being raised in the first instance by using code
which detects the condition which would raise the error.

If you need further assistance on this post back with more details of the
context.

Ken Sheridan
Stafford, England