What expression can I use to return more than two possible values from one field

[joeydiaz]

Greetings all,



I have a field [Contract_Due] that is based on a date the user inputs. The expression I am currently using, (IIf), works fine to return two responses;

Contract_Status: IIf([Contract_Due]<62,"Call Contact","Good")

The Contract_Due value is a number and ranges depending on the difference between the date assigned and the current date().

The True value being "Call Contact" if the Contract_Due value is <62.

The False value is "Good" for anything over 62 days.

While this is a working solution for the time being, I would like to include another criteria. Where if Contract_Due value falls under lets say -40 it should display "Time & Material".

I am fairly new to building expressions so please forgive me if this is an easy solution, but with that said, how would I go about building the expression to either replace the IIf expression or add to it to include the new criteria? I am using Access 2010.

Thanks

[pbaldy]

You can nest one IIf() function within either the true or false argument of another. You might also look at the Switch() function. I might put the data into a table to make it more dynamic.

[joeydiaz]

You can nest one IIf() function within either the true or false argument of another. You might also look at the Switch() function. I might put the data into a table to make it more dynamic.

Thanks Pbaldy! That fixed my problem!

Here's the end formula,

Contract_Status: Switch([Contract_Due]<=-40,"T & M",[Contract_Due]<=62,"Call Contact",[Contract_Due]>62,"Good")

I'll have to remember the switch function. I did'nt really want to try two IIf statements, that seemed like more trouble for me. Switch() worked like a charm!

Thanks again man

[pbaldy]

Happy to help and welcome to the site by the way!