IIf function question

  • Thread starter Thread starter Dennis Villareal
  • Start date Start date
D

Dennis Villareal

this is what it reads now

WARNING: WARNING: IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")

i would like to build a IIF function kinda the same but here is what i would
like to see.

Warning: IIf [BLH_CUS_NO] = 476 or 477 or 479 or 474 or 471 then
IIf([YLD]<60000 Or [TEN]<70000 Or [ELG]<20,"FAIL") if not then
IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")

how would this need to be worded in the query?
 
sorry typo not sure where warning came up twice

WARNING: IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")

that is what i reads now

Dennis Villareal said:
this is what it reads now
Click to expand...
WARNING: IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")
i would like to build a IIF function kinda the same but here is what i would
like to see.

Warning: IIf [BLH_CUS_NO] = 476 or 477 or 479 or 474 or 471 then
IIf([YLD]<60000 Or [TEN]<70000 Or [ELG]<20,"FAIL") if not then
IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")

how would this need to be worded in the query?
Click to expand...
 
this is what it reads now

WARNING: WARNING: IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")

i would like to build a IIF function kinda the same but here is what i would
like to see.

Warning: IIf [BLH_CUS_NO] = 476 or 477 or 479 or 474 or 471 then
IIf([YLD]<60000 Or [TEN]<70000 Or [ELG]<20,"FAIL") if not then
IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")

how would this need to be worded in the query?
Click to expand...

OR doesn't work that way - it's not the English language conjunction, it's a
Boolean algebraic operator that compares two True or False values. 477 isn't a
"true or false" expression - well, actually it is TRUE because it's nonzero,
but it WON'T give you the desired result.

Secondly IIF does not work the way you expect, either. It's a VBA function
with three arguments: an expression, a value to be returned if the expression
is TRUE, and a value to be returned if the expression is FALSE. It does not
contain the words "then" or "if".

Since the values returned can themselves be expressions, try

Warning: IIf([BLH_CUS_NO] = 476 or [BLH_CUS_NO] = 477 or [BLH_CUS_NO] = 479 or
[BLH_CUS_NO] = 474 or [BLH_CUS_NO] = 471,
IIf([YLD]<60000 Or [TEN]<70000 Or [ELG]<20,"FAIL", Null),
IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL", Null))

I'm not certain of your business logic; as it is, *any one* of the three
criteria would cause a FAIL if the condition were met.

I put a NULL result as the alternative if it's not a FAIL. You might want to
change Null to "PASS" or something more appropriate.
 
Dennis said:
this is what it reads now

WARNING: WARNING: IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")

i would like to build a IIF function kinda the same but here is what i would
like to see.

Warning: IIf [BLH_CUS_NO] = 476 or 477 or 479 or 474 or 471 then
IIf([YLD]<60000 Or [TEN]<70000 Or [ELG]<20,"FAIL") if not then
IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")
Click to expand...


Try using something like this in a calculated field in your
query:

MyField: "Warning: " & IIf([BLH_CUS_NO]
IN(476,477,479,474,471), IIf([YLD]<60000 Or [TEN]<70000 Or
[ELG]<20, "FAIL", IIf([YLD]<50000 Or [TEN]<62000 Or
[ELG]<23, "FAIL", "All OK"))

Then post back with a Copy/Paste of what you actually used
and what about the results you don't like. I suspect that
your pseudo expression was ambiguous so try to be explicit
about the input values and their related result.
 
this worked perfect. i have not tested it 100% but it seems to work well. i
will finish the testing phase! thanks alot

John W. Vinson said:
this is what it reads now

WARNING: WARNING: IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")

i would like to build a IIF function kinda the same but here is what i would
like to see.

Warning: IIf [BLH_CUS_NO] = 476 or 477 or 479 or 474 or 471 then
IIf([YLD]<60000 Or [TEN]<70000 Or [ELG]<20,"FAIL") if not then
IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")

how would this need to be worded in the query?
Click to expand...

OR doesn't work that way - it's not the English language conjunction, it's a
Boolean algebraic operator that compares two True or False values. 477 isn't a
"true or false" expression - well, actually it is TRUE because it's nonzero,
but it WON'T give you the desired result.

Secondly IIF does not work the way you expect, either. It's a VBA function
with three arguments: an expression, a value to be returned if the expression
is TRUE, and a value to be returned if the expression is FALSE. It does not
contain the words "then" or "if".

Since the values returned can themselves be expressions, try

Warning: IIf([BLH_CUS_NO] = 476 or [BLH_CUS_NO] = 477 or [BLH_CUS_NO] = 479 or
[BLH_CUS_NO] = 474 or [BLH_CUS_NO] = 471,
IIf([YLD]<60000 Or [TEN]<70000 Or [ELG]<20,"FAIL", Null),
IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL", Null))

I'm not certain of your business logic; as it is, *any one* of the three
criteria would cause a FAIL if the condition were met.

I put a NULL result as the alternative if it's not a FAIL. You might want to
change Null to "PASS" or something more appropriate.
Click to expand...
 
Warning:IIf([BLH_CUS_NO]
IN(476,477,479,474,471), IIf([YLD]<60000 Or [TEN]<70000 Or
[ELG]<20, "FAIL", IIf([YLD]<50000 Or [TEN]<62000 Or
[ELG]<23, "FAIL",null)))

i tried the above and it worked for the 476 477 479 474 471 but then when i
tested the other numbers they were not showing failed for the new specs.

thanks for your help anyways

Marshall Barton said:
Dennis said:
this is what it reads now

WARNING: WARNING: IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")

i would like to build a IIF function kinda the same but here is what i would
like to see.

Warning: IIf [BLH_CUS_NO] = 476 or 477 or 479 or 474 or 471 then
IIf([YLD]<60000 Or [TEN]<70000 Or [ELG]<20,"FAIL") if not then
IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")
Click to expand...


Try using something like this in a calculated field in your
query:

MyField: "Warning: " & IIf([BLH_CUS_NO]
IN(476,477,479,474,471), IIf([YLD]<60000 Or [TEN]<70000 Or
[ELG]<20, "FAIL", IIf([YLD]<50000 Or [TEN]<62000 Or
[ELG]<23, "FAIL", "All OK"))

Then post back with a Copy/Paste of what you actually used
and what about the results you don't like. I suspect that
your pseudo expression was ambiguous so try to be explicit
about the input values and their related result.
Click to expand...
 
Please be explicit. your words "the other numbers they were
not showing failed for the new specs" just don't explain
what "other numbers" and "new specs" are supposed to mean.
You still have to explain what the two inner IIfs are
supposed to accomplish and I am fairly sure I do not
understand what should not result in FAIL.
--
Marsh
MVP [MS Access]


Dennis said:
Warning:IIf([BLH_CUS_NO]
IN(476,477,479,474,471), IIf([YLD]<60000 Or [TEN]<70000 Or
[ELG]<20, "FAIL", IIf([YLD]<50000 Or [TEN]<62000 Or
[ELG]<23, "FAIL",null)))

i tried the above and it worked for the 476 477 479 474 471 but then when i
tested the other numbers they were not showing failed for the new specs.

Marshall Barton said:
Dennis said:
this is what it reads now

WARNING: WARNING: IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")

i would like to build a IIF function kinda the same but here is what i would
like to see.

Warning: IIf [BLH_CUS_NO] = 476 or 477 or 479 or 474 or 471 then
IIf([YLD]<60000 Or [TEN]<70000 Or [ELG]<20,"FAIL") if not then
IIf([YLD]<50000 Or [TEN]<62000 Or [ELG]<23,"FAIL")
Click to expand...


Try using something like this in a calculated field in your
query:

MyField: "Warning: " & IIf([BLH_CUS_NO]
IN(476,477,479,474,471), IIf([YLD]<60000 Or [TEN]<70000 Or
[ELG]<20, "FAIL", IIf([YLD]<50000 Or [TEN]<62000 Or
[ELG]<23, "FAIL", "All OK"))

Then post back with a Copy/Paste of what you actually used
and what about the results you don't like. I suspect that
your pseudo expression was ambiguous so try to be explicit
about the input values and their related result.
Click to expand...
Click to expand...
 
Back
Top