IIf(IsError...

[beppe005]

I am trying to use the two functions IIf and IsError in a query in this way,
which is wrong (since it doesn't work):

Total: IIf(IsError([field1]/[field2];0;[field]/[field2])

my intenttion is that, if the result of the division is "error", for example
because [field2]=0, then value of [Total] is "0", otherwise the result of
the division should be the true result of the division.

TIA
Beppe

 

[John Spencer (MVP)]

In a query you can use

IIF(Field2=0,0,Field1/Field2)

SQL either ignores the error generated by the bad division if field2 is zero or
it never evaluates the false part. I don't know which it is, but this does work.

In a VBA module, you would get an error.

 

[Guest]

I am no expert but I would have done it like this:

Total: IIf(IsError([field1]/[field2]),"0",[field]/[field2])

 

[Michael Keating]

Hi,

There are several things that need changing in the code you posted.

Looking at the existing code, you have 2 open brackets, but you have only 1
close .. effectively the ISERROR function.

You have arguments separated with semi-colons ( ; ) .. arguments should be
separated with commas( , ) when there are more than one

Looking at the logic of what you're trying to do, there is only one error
condition .. that is when the second field contains zero (the first, and
only, commandment of mathematics: Thou Shalt Not Divide By Zero) .. so all
you have to do is check for that condition.

You could achieve what you want with something like:

Total:IIf([field2]=0, 0, [field1]/[field2])

HTH

MFK.

 

[John Vinson]

You have arguments separated with semi-colons ( ; ) .. arguments should be
separated with commas( , ) when there are more than one

Just a clarification - semicolons *are* correct delimiters in many
non-English versions of Access.


John W. Vinson[MVP]
(no longer chatting for now)