Another Nested IIF Query problem

[Brian Collins]

Testing for two conditions and using AND/NOT/OR

WIPTest:

IIF([AutoMatrix] =1 And [Status] ="Settled Adjudicated",0
IIF([AutoMatrix] =2 Or [AutoMatrix]= 4 And [Status] Not "Settled adjudicated", 555
IIF([AutoMatrix] =3 Or [AutoMatrix]= 5 And [Status] Not "Settled adjudicated", 999,[WIP])))

Condition one works fine but provides the False for all other conditions.

Am I messing the logic up? Is there an heirarchy?

Confused



Regards

Brian

[slave138]

I think you're missing some commas:

 
IIF([AutoMatrix] =1 And [Status] ="Settled Adjudicated",0,
IIF([AutoMatrix] =2 Or [AutoMatrix]= 4 And [Status] Not "Settled adjudicated", 555,
IIF([AutoMatrix] =3 Or [AutoMatrix]= 5 And [Status] Not "Settled adjudicated", 999,[WIP])))

[valbor]

Here is the syntax which should work in my opinion:
IIF([AutoMatrix] =1 And [Status] ="Settled Adjudicated",0,
IIF(([AutoMatrix] =2 Or [AutoMatrix]= 4) And [Status] Not "Settled adjudicated", 555,
IIF(([AutoMatrix] =3 Or [AutoMatrix]= 5) And [Status] Not "Settled adjudicated", 999,[WIP])))

[Brian Collins]

Thanks guys,


Slave, I missed the commas out on the post but not in the statement thanks for trying.

Valbor - I tried using brackets to combine the OR condition

Same result

Condition one provides the correct result

Remaining conditions from the false part of the function.

Tired now will play with it tomorrow

Many thanks

B

[slave138]

I got it to work in testing using this:

Sub TestIIf(bSuccess1 As Boolean, bSuccess2 As Boolean, bSuccess3 As Boolean)
    MsgBox IIf(bSuccess1 = True, 0, IIf(bSuccess2 = True, 1, IIf(bSuccess3 = True, 2, "All were false")))
End Sub

I got the expected responses each time.

[slave138]

You might want to look at the "Not" usage in your code because I don't think it is working the way you expect. I would change it to "<>" and see if that works any better.

[slave138]

Corrected code:

IIF([AutoMatrix] =1 And [Status] ="Settled Adjudicated",0,
IIF([AutoMatrix] =2 Or [AutoMatrix]= 4 And [Status] <> "Settled adjudicated", 555,
IIF([AutoMatrix] =3 Or [AutoMatrix]= 5 And [Status] <> "Settled adjudicated", 999,[WIP])))

[Brian Collins]

Hi Slace,

Well done, the <> construct works a treat.

Although I am at a loss to why 'NOT' does not do what it says on the tin!

Many thanks indeed.

B

[slave138]

Not might have worked if you had used = like:

IIF([AutoMatrix] =2 Or [AutoMatrix]= 4 And (NOT [Status] = "Settled adjudicated)",...,...)

But as it was written, Access reinterpets it as:

IIF(NOT [AutoMatrix] =2 Or [AutoMatrix]= 4 And [Status],...,...)