IIF function for 3 criteria, possible?

[Cam]

Hello,

I am trying to do a query where I need a result based on 3 criteria, but not
sure how to do it. in written term would be like this for a new fields
(column in query).
1) If OprStat = 1 and StartDate < today date, return "LateN"
2) If OprStat = 3 and StartDate < today date, return "LateM"
3) If does not fall into (1) & (2) condition, return "OnTime"

Sample Data:
Order OprStat StartDate
1110 1 4/1/2010
1111 1 4/12/2010
1112 3 4/2/2010

Results: assuming current date is 4/6/2010.
Order OprStat StartDate Result
1110 1 4/1/2010 LateN
1111 1 4/12/2010 OnTime
1112 3 4/2/2010 LateM

 

[KARL DEWEY]

Try this --
IIF([OprStat] = 1 and [StartDate] < Date(), "LateN", IIF([OprStat] = 3 and
[StartDate] < Date(), "LateM", "OnTime"))

 

[david]

SWITCH
(OprStat = 1 and StartDate < today date), "LateN",
(If OprStat = 3 and StartDate < today date), "LateM",
True, "OnTime"


For multiple choices, SWITCH may be clearer than nested IIFs

(david)