IIF statement in query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cdolphin88@yahoo.co.uk - 07 Sep 2006 14:46 GMT

Hello,

I have the query below that I used an IIF statement to classified the
different band..

Is that possible to show me in only one column the different
classification? Because now it shows in 5 different column..

the query I'm using is like this:

SELECT band, IIF(band = 1, 'Very Low Priority'), IIF(band = 2, 'Low
Priority'), IIF(band = 3, 'Medium Priority'), IIF(band = 4, 'High
Priority'), IIF(band = 5, 'Very High Priority')
FROM Table1;

Cheers

Reply to this Message

Graham R Seach - 07 Sep 2006 15:06 GMT

SELECT band, IIf(band=1,"Very Low Priority", IIF(band=2,"Low Priority",
IIF(band=3,"Medium Priority", IIF(band=4,"High Priority","Very High
Priority")))) As Priority FROM Table1

...or...

SELECT band, Choose(band,"Very Low Priority", "Low Priority", "Medium
Priority", "High Priority","Very High Priority") As Priority FROM Table1

...or...

SELECT band, Switch(band=1,"Very Low Priority", band=2,"Low Priority",
band=3,"Medium Priority", band=4,"High Priority",band=5,"Very High
Priority") As Priority FROM Table1

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

> Hello,
>
[quoted text clipped - 12 lines]
>
> Cheers

Reply to this Message

Duane Hookom - 07 Sep 2006 15:18 GMT

Or don't use an expression at all. Create a small lookup table
tblPriorities
=============================
Band
Priority
With values like
1 Very Low Priority
2 Low Priority
...

You can add this table to your query and join the band fields.

Signature

Duane Hookom
MS Access MVP

> SELECT band, IIf(band=1,"Very Low Priority", IIF(band=2,"Low Priority",
> IIF(band=3,"Medium Priority", IIF(band=4,"High Priority","Very High
[quoted text clipped - 33 lines]
>>
>> Cheers

Reply to this Message

Graham R Seach - 07 Sep 2006 15:27 GMT

Ahh, the simplest methods are always the best! :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

> Or don't use an expression at all. Create a small lookup table
> tblPriorities
[quoted text clipped - 45 lines]
>>>
>>> Cheers

Reply to this Message