IIF AND Multiple (1 Viewer)

livmun

New member
Local time
Today, 05:41
Joined
Jun 1, 2015
Messages
4
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

Well-known member
Local time
Today, 01:41
Joined
Apr 9, 2015
Messages
4,339
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.

Code:
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

Super Moderator
Staff member
Local time
Today, 05:41
Joined
Feb 19, 2013
Messages
16,553
'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

Banishment Pending
Local time
Today, 00:41
Joined
May 11, 2011
Messages
11,613
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'
 

Users who are viewing this thread

Top Bottom