Solved IIF 3 conditions

[The Rev]

I am trying to get a total deducted field in my query. Here is my SQL statement that doesn't work...

TotalDeducted: IIF([Requirement_Satisfied]=True, "0", IIF(([Requirement_Other_Than_Satisfied]=True),[Requirement_Score], ([Requirement_Special_Considerations_Satisfied=True], [Requirement_Special_Considerations_Score]))

I am trying to evaluate 3 distinct conditions. Only one can be met. If the Requirement is satisfied, I want the Total deducted to be 0, if it is "Other than satisfied", I want the value from "Requirement_Score", and if only the "Special_Considerations_Satisfied" is met, I want the value from Requirement_Special_Considerations_Score.

 

[vba_php]

If the Requirement is satisfied, I want the Total deducted to be 0, if it is "Other than satisfied", I want the value from "Requirement_Score", and if only the "Special_Considerations_Satisfied" is met, I want the value from Requirement_Special_Considerations_Score.

you have forgotten the "IF FALSE" argument portions of the function! =) (not to mention a syntax error you probably didn't see)

IIF([Requirement_Satisfied]=True, "0",

iif([Requirement_Other_Than_Satisfied]=True,[Requirement_Score],

iif([Requirement_Special_Considerations_Satisfied]=True, [Requirement_Special_Considerations_Score], 0)))

that might solve your issue, and it might not. it sounds to me like what you want could be a lot more complicated than what I just gave you. your description is a bit unclear as to what you need, actually. but give that a try first.

 

[namliam]

making code readable, what a novel idea, dont forget to remove the surplus ")"

 

[vba_php]

making code readable, what a novel idea, dont forget to remove the surplus ")"

who you talking to?

 

[namliam]

me myself and I and maybe the ts certainly not your perfect readable code

 

[vba_php]

me myself and I and maybe the ts certainly not your perfect readable code

is that a dig on a christian brother? =( nothing is perfect.

 

[The Rev]

you have forgotten the "IF FALSE" argument portions of the function! =) (not to mention a syntax error you probably didn't see)

IIF([Requirement_Satisfied]=True, "0",

iif([Requirement_Other_Than_Satisfied]=True,[Requirement_Score],

iif([Requirement_Special_Considerations_Satisfied]=True, [Requirement_Special_Considerations_Score], 0)))

that might solve your issue, and it might not. it sounds to me like what you want could be a lot more complicated than what I just gave you. your description is a bit unclear as to what you need, actually. but give that a try first.

Worked like a charm! Exactly what I needed it to do. Thanks brother!!

 

[MajP]

In the first case you return a nz value of "0" but all else seem to be actual numeric. If you meant 0 then use 0, if not it may sort that whole column as string.

 

[namliam]

is that a dig on a christian brother? =( nothing is perfect.

certainly no dig, maybe a reference to you fixing your post

Also, having readable code is a personal pet peave of mine that really gets my blood boiling.
I just cannot understand how people in this case post a double nested IIF without alligning it and making it readable.
Readable code in 9 times out of 10 will prevent errors like this.

At my workplace my name has become a proverb for making code readable, readable code is maintainable code

 

[vba_php]

I just cannot understand how people in this case post a double nested IIF without alligning it and making it readable.

I'm not sure, mailman. are you saying that mine is good or the OP's is good?

 

[plog]

I am trying to evaluate 3 distinct conditions. Only one can be met.

The second sentence contradicts the first. If only one can be, they are not distinct, they are related. Nearsightedness, Eczema and deafness are 3 distinct conditions; having one has no bearing on having the other 2.

Being left hand dominant, right hand dominant, ambidextrous and handless are mutually exclusive; you can only be and must be one. In a database I would not store Left Handed/Right Handed/Ambidextrous/Handless in their own fields. I would have one field named [DominantHand] and those 4 values would be the values acceptable to that field.

If your 3 conditions are mutually exclusive (as in the dominant hand example) I believe you might be storing your data incorrectly for this. Instead of 3 boolean fields to say which of those 3 are meet, you should have just 1 text field that says which condition is meet. Then if the related score for each of those 3 values is the same for all records you would have a table to hold those scores and link to it instead of using your complex IIF statements.