iif statement

[mike4100]

I have the following statements and keep getting "YES" where I should be
getting "WAIVED" or "NO". Does anyone know why, everything else evaluates correctly.

IIf([admit_dec_code] Is Null Or
[admit_dec_code]="R","",IIf([admit_dec_code] Is Not Null Or
[admit_dec_code]<>"R" And [date_acc_fee_paid] Is Not
Null,"YES",IIf([admit_dec_code] Is Not Null Or [admit_dec_code]<>"R" And
[date_acc_fee_paid] Is Null And [accept_fee_amount]="1","WAIVED","NO")))


IIf(IsNull([admit_dec_code]) Or [admit_dec_code]="R","",IIf(Not IsNull([admit_dec_code]) Or [admit_dec_code]<>"R" And Not IsNull([date_acc_fee_paid]),"YES",IIf(Not IsNull([admit_dec_code]) Or [admit_dec_code]<>"R" And IsNull([date_acc_fee_paid]) And [accept_fee_amount]="1","WAIVED","NO")))

 

[Rickster57]

Iif

Remember that in an IIF statement in a query the very FIRST instance of a condition being true will get the assigned value and basically exit out of the query. Try putting your most complex IIF conditions first in your query.

Good luck and go Browns!!

 

[mike4100]

I would do that but I really can't using the logic I have been given. How would you rewrite it?

 

[Rickster57]

iif

Create another expression in your query, anothertry:

You don't need to mess with the logic you were given (aasuming it is correct of course), just the order you want the conditions to be read.

anothertry:IIf(Not IsNull([admit_dec_code]) Or [admit_dec_code]<>"R" And IsNull([date_acc_fee_paid]) And [accept_fee_amount]="1","WAIVED",iif((Not IsNull([admit_dec_code]) Or [admit_dec_code]<>"R" And Not IsNull([date_acc_fee_paid]),"YES",IIf((IsNull([admit_dec_code]) Or [admit_dec_code]="R","","NO"))).....something like this where your most complex statement if true will exit out of the query. So in this instance if the first iif statement is true anothertry will be given the value of "Waived" and it stops evaluating any further. If this isn't true then it evaluates the next iif statement and if true then anothertry = "Yes" and doesn't matter what the next series of IIF statements are.


Hopefully this will help.

 

[mike4100]

I've tried a number of things and nothing is working. I guess I need to try this a different way maybe a function. Thanks for helping.

 

[pbaldy]

Perhaps if you posted the business rules the formula is supposed to implement, someone can correct it. My gut is that it has to do with the AND's & OR's, but impossible to say without knowing the required logic.

 

[grnzbra]

I believe this combination will always evaluate to True:

IIf([admit_dec_code] Is Not Null Or
[admit_dec_code]<>"R"

If it is Null, it is not "R". If it is "R" or anything else, it is not Null. (Of course, if it is null or = "R", you would never get to the next IIf statement.) I don't know how Access would process that, but you might want to put parenthases arouund each of those OR/AND combinations to make sure it processe it the way you want.

I think that after

IIf([admit_dec_code] Is Null Or
[admit_dec_code]="R","",

you might try not checking for Nullness or Rness because you already know that it is not null and not R.