Nest iif - can't get the syntax right!

[Julia Boswell]

Hi, I wonder if anyone can help, I've tried many varieties of syntax but
none of them work and I just can't get my brain round this one, if it was an
if then else statement in VB I'd be fine! I want to create a field called BU
to display the following on a query:

Iif [BUName] is "Defence" then BU = "GS"
Iif [BUName] is "On Board" or "Service Centre" or "STS" then BU = "RCS"
Iif [BUName] is "eFlight" then BU = "CS"
Iif [BUName] is "ATS" or "BRS" then BU = "Lodgers"
else BU = "Support"

p.s. this isn't how I wrote it in the query, this is just what I want it to
do!

Can someone help?

Thanks

Julia

 

[John Spencer]

Nested IIF would look like

IIF(BuName = "Defence", "GS",
IIF(BuName IN("On Board","Service Centre","STS"), "RCS",
IIF(BuName = "eFlight","CS",
IIF(BuName in ("ATS","BRS"),"Lodgers","Support"))))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Nurick]

IMHO this is easier to read:

BU = SWITCH(
[BUName] = 'Defence', 'GS',
[BUName] IN ('On Board', 'Service Centre', 'STS'), 'RCS',
[BUName] = 'eFlight', 'CS',
[BUName] = IN ('ATS', 'BRS'), 'Lodgers',
-1, 'Support
)

but maintenance would be simpler if you put these rules into a table:

BUName, BU
Defence, GS
OnBoard, RCS
Service Centre, RCS
STS, RCS
...

and join it into the query. That way, a change in the organisation would
just mean editing the table rather than modifying your code.

Hi, I wonder if anyone can help, I've tried many varieties of syntax but
none of them work and I just can't get my brain round this one, if it was an
if then else statement in VB I'd be fine! I want to create a field called BU
to display the following on a query:

Iif [BUName] is "Defence" then BU = "GS"
Iif [BUName] is "On Board" or "Service Centre" or "STS" then BU = "RCS"
Iif [BUName] is "eFlight" then BU = "CS"
Iif [BUName] is "ATS" or "BRS" then BU = "Lodgers"
else BU = "Support"

p.s. this isn't how I wrote it in the query, this is just what I want it to
do!

Can someone help?

Thanks

Julia

 

[Julia Boswell]

Thanks everyone. It's sorted now
Julia