IIF AND Multiple

[livmun]

Hi, I'm attempting to categorise my data using nested IIf AND functions in query design but it's not working. Can anyone help?

Priority: IIf([ColumnA] = "*abc*","P1",IIf(([ColumnA]="*def*") And ([ColumnB] Is Not Null),"P2"),IIf(([ColumnA]="*def*") And ([ColumnB] is null, “P3")

 

[Ranman256]

rather than nest IIFs which is complex and easily to error,
Either:
make nested queries (UNION query) to get the results
or
make a custom formula to parse the field into the correct buckets.

function getCode(pvColA, pvColB)
select case true
    case instr(pvColA,"abc")>0 
          getCode = "P1"
    case instr(pvColA,"def")>0 and not IsNull(pvColB)
          getCode = "P2"
    case instr(pvColA,"def")>0 and IsNull(pvColB)
          getCode = "P3"
end select
end function

 

[CJ_London]

'it's not working' isn't very helpful

but as a guess

IIf([ColumnA] = "*abc*","....

you are not looking for a value of '*abc*' but something that contains 'abc'

if you are then you should use Like

IIf([ColumnA] Like "*abc*","....

Only use = when looking for a specific value, in which case it would be

IIf([ColumnA] = "abc","....

 

[plog]

1. When you get that nested its time for a custom function.

2. Your logic isn't complete. Meaning, it's possible for Priority to be NULL.

My advice is to write out what you hope to accomplish in English, then convert that to code:


If A contains 'abc' its 'P1'
If A contains 'def' and B has a value it's 'P2'
If A contains 'def' and B doesn't have a value its 'P3'
If no value assigned up until now its 'Error'