IIF Statement too complex

[JAFO]

This is what my IIF Statment looks like

Density: = IIf([mtype]="M3596",0.0412,IIf([mtype]="m3571",0.0487,IIf([mtype]="m3592",0.0412,IIf([mtype]="m2279",.0426,IIf([mtype]="m4692",.0499,IIf([mtype]="m4693",.0419,IIf([mtype]="m4695",.0473,IIf([mtype]="m2348",.0473,IIf([mtype]="m4696",.0509,IIf([mtype]="m4629",.0524,IIf([mtype]="m4698",.0553,IIf([mtype]="m2060",.0433,IIf([mtype]="m2097",.0325,IIf([mtype]="m2043",.0473,IIf([mtype]="m2956",.0390,IIf([mtype]="m4627",.039,IIf([mtype]="m4646".0422,IIf([mtype]="m3586",.0398,IIf([mtype]="m2965",.043,IIf([mtype]="m4628",.0441,IIf([mtype]="m2396",.0531,IIf([mtype]="m4691".0453,"No")))))))))))))))))))))

When I cut it down to

Density: IIf([mtype]="M3596",0.0412,IIf([mtype]="m3571",0.0487,IIf([mtype]="m3592",0.0412,IIf([mtype]="m2279",0.0426,IIf([mtype]="m4692",0.0499,IIf([mtype]="m4693",0.0419,IIf([mtype]="m4695",0.0473,IIf([mtype]="m2348",0.0473,IIf([mtype]="m4696",0.0509,IIf([mtype]="m4629",0.0524,IIf([mtype]="m4698",0.0553,IIf([mtype]="m2060",0.0433,IIf([mtype]="m2097",0.0325,IIf([mtype]="m2043",0.0473,"No"))))))))))))))

A2k accepts it.

It's used in a query.

Any ideas on how can I make the first version work?

Thanks for the help!

 

[KenHigg]

Where are you using this? In a query? In some vba?

???
kh

 

[KKilfoil]

You may be better off by creating a 'lookup' table with fields for MT_ID, MType,LookupValue, and then relating this table into your query.

This eliminates your extremely ugly IIf expression, and makes maintenance MUCH easier, as you can add, change, or delete the lookup records very easily.

Let us know if you need more detail on this...

 

[pdx_man]

Check out the SWITCH function.

SWITCH([mtype]="M3596",0.0412,[mtype]="m3571",0.0487, ...