IIF problem inside Select

[ping99]

Hi all,

I have a select SQL with more than 13 IIF inside that always
gave an error message :

Complex expression WHY ????

Maybe Access 2000 can handle only 13 IFF,

Please give some advices or ideas how to solve
the following Select :

select

iif(mid([fieldName],1,4)="0602","aaa",
iif(trim([fieldName]) in ("0603011"),"bbb",
iif(trim([fieldName]) in ("0603021","0603022","0603026"),"ccc",
iif(trim([fieldName]) in ("0603031","0603032"), "ddd",
iif(trim([fieldName]) in ("0603041","0603042"),"eee",
iif(trim([fieldName]) in ("0603072","0603081","0603082"),"fff",
iif(trim([fieldName]) in ("0603161","0603226"),"ggg",
iif(trim([fieldName]) in ("0699011"), "hhh",
iif(trim([fieldName]) in ("0802011","0831016"),"iii",
iif(trim([fieldName]) in ("1004013"), "jjj",
iif(mid([fieldName],1,4)="0631","kkkk",
iif(mid([fieldName],1,4)= "0701","llllllllllll",
iif(mid([fieldName],1,4)="0702", "mmmmmmmmmmmmm",
"blank"))))))))))))) AS fieldnameNew FROM mytable;

Thank you for any contribution.

 

[r937]

build yourself a lookup table and do a join

r937.com | rudy.ca

 

[ping99]

Can somebody gave an example how to built look up table with a join ?

Thank in advance.

 

[r937]

create table lookup
( lookupcode  varchar(9) not null primary key
, lookupvalue varchar(37) not null
)
insert into lookup values ('0602'   ,'aaa')
insert into lookup values ('0603011','bbb')
insert into lookup values ('0603021','ccc')
insert into lookup values ('0603022','ccc')
insert into lookup values ('0603026','ccc')
insert into lookup values ('0603031','ddd')
insert into lookup values ('0603032','ddd')
insert into lookup values ('0603041','eee')
insert into lookup values ('0603042','eee')
insert into lookup values ('0603072','fff')
insert into lookup values ('0603081','fff')
insert into lookup values ('0603082','fff')
insert into lookup values ('0603161','ggg')
insert into lookup values ('0603226','ggg')
insert into lookup values ('0699011','hhh')
insert into lookup values ('0802011','iii')
insert into lookup values ('0831016','iii')
insert into lookup values ('1004013','jjj')
insert into lookup values ('0631'   ,'kkkk')
insert into lookup values ('0701'   ,'llllllllllll')
insert into lookup values ('0702'   ,'mmmmmmmmmmmmm')

select ...
  from yourtable
inner
  join lookup
    on mid([fieldName],1,4) = lookupcode
    or trim([fieldName]) = lookupcode

r937.com | rudy.ca

 

[ping99]

Thank you,

It worked fine after testing but I must built a long lookup table with many values.

 

[Golom]

... but I must built a long lookup table with many values

You have many values. No way around that.

Yes ... you do have to build the table ... but that's infinitely preferable to blowing SQL's mind with a never-ending set of IIF statements.

 

[madelca100]

I know there is a workaround offered but I thought I'd check the original code.

I substituted a valid fieldname and ran this in Access 97 - the only version I have.

The code works fine.