iif in access query

[cluser]

I have a query similar like this to get a institutionRole field, but it is in progress sysntax, now I want to do same thing in Microsoft Access query,it seems I can only use iif, but I don't know how to do it, it has multiple arguments,

IF (ENT = "074") THEN ("MHSparent") ELSE ((IF (ENT = "075") THEN ("MMEparent") ELSE ((IF (ENT = "175") THEN ("MMWparent") ELSE ((IF (ENT = "601") THEN ("CSparent") ELSE ((IF (ENT = "602") THEN ("DHparent") ELSE ((IF (ENT = "603") THEN ("EXparent") ELSE ((IF (ENT = "604") THEN ("GRVparent") ELSE ((IF (ENT = "605") THEN ("MWAparent") ELSE ((IF (ENT = "606") THEN ("SHEparent") ELSE ((IF (ENT = "900") THEN ("DECparent") ELSE ((IF (ENT = "000") THEN ("DSCparent") ELSE ("Parent")))))))))))))))))))))


Thanks for any help

 

[GingerR]

My suggestion would be to build a table with 2 fields


ENT DESC
074 MHSparent
075 MMEParent
175 MMWparent
601 CSparent

etc.

This way you can just make a join to this table on the field ENT, and display the description. In the future, it's easy then to just add new records to this lookup table than it would be to go find each and every query and report that contains this information and add to the code.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[cluser]

That's a good idea, but for the last parameter, meaning after all checked, else parent,

how can I do it in the table,

Thanks.

 

[GingerR]

make the join arrow point from the main table to the new lookup table on the field ENT. In your calculated field in the query, put something like

Description: iif(LookupTable!ENT is null,"Parent",[LookupTable]![Desc])

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[cluser]

Great ideas,

Thanks