Building an iif() expression

[Sephaerius]

I'm building an expression, and trying to streamline the syntax. When I use nested iif()s, it works perfectly, however when I try to streamline using Or, it fails. Am I doing something wrong, or is this a limitation of the iif() function?



Working example:

IIf([tbl_holds_stage1].[Account Type]="O.ER" And [tbl_holds_stage1].[ReasonForHold1]="ADM DX","MIRAMED",
IIf([tbl_holds_stage1].[Account Type]="O.OP" And [tbl_holds_stage1].[ReasonForHold1]="ADM DX","MIRAMED",
IIf([tbl_holds_stage1].[Account Type] Like "O.RE*" And [tbl_holds_stage1].[ReasonForHold1]="ADM DX","MIRAMED",
IIf([tbl_holds_stage1].[Account Type]="O.ASU" And [tbl_holds_stage1].[ReasonForHold1]="ADM DX","MIRAMED",
IIf([tbl_holds_stage1].[Account Type]="O.ER" And [tbl_holds_stage1].[ReasonForHold1] Like "DX1*","MIRAMED",
IIf([tbl_holds_stage1].[Account Type]="O.OP" And [tbl_holds_stage1].[ReasonForHold1] Like "DX1*","MIRAMED",
IIf([tbl_holds_stage1].[Account Type] Like "O.RE*" And [tbl_holds_stage1].[ReasonForHold1] Like "DX1*","MIRAMED",
IIf([tbl_holds_stage1].[Account Type]="O.ASU" And [tbl_holds_stage1].[ReasonForHold1] Like "DX1*","MIRAMED",
[teams].[JBH]))))))))

Non-working example:

IIf([tbl_holds_stage1].[Account Type]="O.ER" Or 
[tbl_holds_stage1].[Account Type]="O.OP" Or 
[tbl_holds_stage1].[Account Type] Like "O.RE*" Or 
[tbl_holds_stage1].[Account Type]="O.ASU" And 
[tbl_holds_stage1].[ReasonForHold1]="ADM DX" Or 
[tbl_holds_stage1].[ReasonForHold1] Like "DX1*","MIRAMED",
[teams].[JBH])

Thanks,

[aytee111]

You might want to put brackets around the "And". When you mix And's and Or's then without brackets the And applies to everything.
Or (xxx AND xxx) Or

[ranman256]

If you have this many IIF, then you need to use a lookup table in a query.
Put all these [Account Type] s in the tAcctType table, and join it to the tData table.
No IIFs needed, no code needed.

[Sephaerius]

You might want to put brackets around the "And". When you mix And's and Or's then without brackets the And applies to everything.
Or (xxx AND xxx) Or

I'm sorry, I don't quite follow. Could you please elaborate?

[Sephaerius]

If you have this many IIF, then you need to use a lookup table in a query.
Put all these [Account Type] s in the tAcctType table, and join it to the tData table.
No IIFs needed, no code needed.

I'm doing that, but these expressions are for specific exceptions to the overall table lookups.

[aytee111]

IIf([tbl_holds_stage1].[Account Type]="O.ER" Or 
[tbl_holds_stage1].[Account Type]="O.OP" Or 
[tbl_holds_stage1].[Account Type] Like "O.RE*" Or 
([tbl_holds_stage1].[Account Type]="O.ASU" And 
[tbl_holds_stage1].[ReasonForHold1]="ADM DX") Or 
[tbl_holds_stage1].[ReasonForHold1] Like "DX1*","MIRAMED",
[teams].[JBH])

[aytee111]

To make it easier. begin with reason for hold:
IIf([tbl_holds_stage1].[ReasonForHold1]="ADM DX" AND ([tbl_holds_stage1].[Account Type]="O.ER" OR ([tbl_holds_stage1].[Account Type]=............)
OR [tbl_holds_stage1].[ReasonForHold1] Like "DX1*" AND (....)

You can also use IN:
IIf((accounttype IN("O.ER","O.OP"...) Or accounttype LIKE "O.RE*") AND (reason="ADM DX" OR reason LIKE "DK1*"),"MIRAMED",teams.JBH)

[Sephaerius]

IIf([tbl_holds_stage1].[Account Type]="O.ER" Or 
[tbl_holds_stage1].[Account Type]="O.OP" Or 
[tbl_holds_stage1].[Account Type] Like "O.RE*" Or 
([tbl_holds_stage1].[Account Type]="O.ASU" And 
[tbl_holds_stage1].[ReasonForHold1]="ADM DX") Or 
[tbl_holds_stage1].[ReasonForHold1] Like "DX1*","MIRAMED",
[teams].[JBH])

I'm afraid this didn't work.

What I'm looking for is any combination between types O.ER, O.OP, O.RE*, O.ASU, that occurs with any reason ADM DX, DX1*.
I'm hoping to string that together in a more efficient format, rather than nesting an iif() statement for each possible combination.

[Sephaerius]

You can also use IN:
IIf((accounttype IN("O.ER","O.OP"...) Or accounttype LIKE "O.RE*") AND (reason="ADM DX" OR reason LIKE "DK1*"),"MIRAMED",teams.JBH)

This sounds promising... let me build and test that one out and I'll report back.

[aytee111]

See my next post

[Sephaerius]

See my next post

Success! The IN() did the trick. Thank you so much!

[aytee111]

The key in this is having the correct bracketing, remember in future - do not mix AND's and OR's without them.