What's the problem here.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jlepack - 20 Dec 2006 17:55 GMT

flag1:iif(ht,iif(avg>t,iif(hu,iif(avg>u,3,2),1),iif(avg<t,iif(hl,iif(avg<l,3,2),1),1),0)

I put the above statement in the field of my query. This statment is
supposed to determine whether the value "avg" is on target, within
control, or out of control.

ht = has target
hu = has upper threshold
hl = has lower threshold
t = target value
u = upper threshold value
l = lower threshold value

3 = out of control
2 = within control
1 = on target
0 = not measured (no target)

When I execute the statement it tells me that the function has the
wrong number of arguments. Why is this?

Reply to this Message

Marshall Barton - 20 Dec 2006 18:22 GMT

>flag1:iif(ht,iif(avg>t,iif(hu,iif(avg>u,3,2),1),iif(avg<t,iif(hl,iif(avg<l,3,2),1),1),0)
>
[quoted text clipped - 16 lines]
>When I execute the statement it tells me that the function has the
>wrong number of arguments. Why is this?

Break it down and count the parenthesis.

This tells me, you are missing the false part of the first
or second iif:

iif(ht,
iif(avg>t,
iif(hu,
iif(avg>u,3,2),
1),
iif(avg<t,
iif(hl,
iif(avg<l,3,2),
1),
1),
0)

Signature

Marsh
MVP [MS Access]

Reply to this Message

David F Cox - 20 Dec 2006 18:25 GMT

avg is an access reserved word Avg() function.
[avg] may work, but you should not use it as a field name.

> flag1:iif(ht,iif(avg>t,iif(hu,iif(avg>u,3,2),1),iif(avg<t,iif(hl,iif(avg<l,3,2),1),1),0)
>
[quoted text clipped - 16 lines]
> When I execute the statement it tells me that the function has the
> wrong number of arguments. Why is this?

Reply to this Message