Replacing many iif by something better

[Newman]

I have a customer who made a query with a field that looks like that:

NewField: iif(Field1=True and Field2>100 and isnull(Field3);"Blue";iif(Field1=True and Field2>100 and not isnull(Field3);"Black";iif(Field1=True and Field2=100 and isnull(Field3);"White";... And so on, for miles and miles.

Although it works, and fast enough, I don't like the way it is displayed and I am sure that it could be even faster without all these iif.

Any idea about the best way for me to handle this kind of query?
Thanks!

 

[boblarson]

If there are that many IIf's then I would suggest creating a function to do the work and reference that in the query.

 

[Newman]

Thank you mister Larson