Help on complex IIf statement please!

[Madmonty]

I am trying to decode some set data (in codes) to a description. In Excel I would use a VLookup, but am struggling to write the expression in Access. I have built this into a column in a query, but Access is saying that the 'expression is too complex'. Any help on overcoming this would be greatly appreciated.

SSADESC: (IIf([SSA2011] Like "01.3*","Health and Social Care",(IIf([ssa2011] Like "01.5*","Child Development and Well Being",(IIf([SSA2011] Like "04.1*","Engineering",(IIf([SSA2011] Like "04.3*","Transportation Operations and Maintenance",(IIf([SSA2011] Like "05.2*","Building and Construction",(IIf([SSA2011] Like "07.4*","Hospitality and Catering",(IIf([SSA2011] Like "15.*","Business Administration",(IIf([SSA2011] Like "01.4*","Public Services",(IIf([SSA2011] Like "07.3*","Service Enterprises",(IIf([SSA2011] Like "08.1*","Sport, Leisure and Recreation,(IIf([SSA2011] Like "13.2*","Direct Learning Support",""))))))))))))))))))))))

thanks in advance.

[patjones]

I would say that the approach here is fundamentally flawed. It looks like you're trying to implement a lookup table using a nested IIf structure.

The way to do this in Access is to create a lookup table with the codes in one column, and the corresponding program descriptions in a second column. Once you have this, you would build it into the record source for your report using a JOIN.

It will be counterproducti ve for you to continue trying to make the IIf statement work. Let us know what you think; if this seems like a rational approach to you then we'll refine the solution some more.

Pat

[Madmonty]

zepphead! many thanks! I built a table with all the possible variations of the codes and linked it to the main data via a joined query. It now works a treat!! Thank you! :-)

[patjones]

That sounds great, and it's really no problem - it's what we're here for. Let us know if any other issues crop up.