Access SQL equivalent of Oracle DECODE 2

[klm2klm2]

What is the Access SQL equivalent of Oracle's DECODE function?

Oracle's DECODE has a syntax something like this:

select
decode(Col1,'A','Active','I','Inactive','Other')
from SomeTable;

Translated into pseudo-code, the DECODE might read:

If Col1 = "A", then return "Active", else
If Col1 = "I", then return "Inactive", else
return "Other"

Thanks,
Kim

 

[dodge20]

The decode function changes the appearance of the selected data. decode requires the name of the column or an expression, followed by pairs of search criteria, and the value to be returned. Optionally a default value can be given that is returned if none of the criteria are satisfied. (The null value is returned if no default is given.) In the example, if sex is ‘m’, ‘male’ is returned; if sex is ‘f’, ‘female’ is returned; if sex is neither ‘m’ or ‘f’, the value ‘unknown’ is returned.

Oracle SQL>
select decode(sex, ’m’, ’male’, ’f’,
’female’, ’unknown’) "Sex",
count(sex) "Number"
from VICTIM
group by sex;

Sex Number
------ -------
female 84
male 455

Access
Note that iif can only be used to give 2 different values unlike decode.
isnull function to test if a value is null eg

iif(isnull(x),'nothing','something')
field gender:iif(sex="m","male","female&quot sex
total group by count
sort
show X X
criteria
or

SELECT IIf([sex]="m","male","female&quot AS gender, Count(victim.sex) AS [number]
FROM victim
GROUP BY IIf([sex]="m","male","female&quot
WITH OWNERACCESS OPTION;


Dodge20

 

[ALEKSJAY]

I think the SWITCH function resembles somehow the Oracle's DECODE function