Error Nested IIF (wrong number of arguments, pls help)

[MsLady]

Hello friends,

I have this as a control source on a txtbox on my report.
I get the error, "The expression you entered contains the wrong number of arguments"

 =IIF(([MinOfStatusCode]=0,"BOR/Scope",IIF([MinOfStatusCode]=1,
"Pri",IIF([MinOfStatusCode]=2,"In Prog",IIF([MinOfStatusCode]=3,"Compl",
IIF([MinOfStatusCode]=4,"Held",IIF([MinOfStatusCode]=5,"Not Strt",
IIF([MinOfStatusCode]=6,"Defer",IIF([MinOfStatusCode]=7,"UAT",
IIF([MinOfStatusCode]>7,"Closed",""))))))))))

Here's what i am trying to get:
statusCode --- shortStat
0 = BOR/Scope
1 = Pri
2 = In Prog
3 = Compl
4 = Held
5 = Not Strt
6 = Defer
7 = UAT
8 = Closed
9 = Reqmts


Help me here please? I am stuck *begging*

 

[KeithG]

The only thing I can think of is that maybe Access dosen't like the "" in the last IIF function. What I would do if I were you is create another tblStatue with two fields StatusCode and ShortStat. Then you can add this table to your qeury and create a join on StatusCode and MinOfStatusCode.

 

[pbaldy]

I suspect it's the 2 opening parentheses.

 

[MsLady]

Hehehe...
You were both right.

First i took out the "" in the last IIF function, then i got another error about my parenthesis, then i took out one of the front "((", and IT'S FIXED!!!

thanks quality gurus!

 

[raskew]

Hi -
Using the Choose() function could eliminate all of those nested IIFs and make it much less prone to error. Example from the debug (immediate) window:

MinOfStatusCode = 4
? iif(MinOfStatusCode >7, "Closed", choose(MinOfStatusCode + 1, "BOR/Scope","Pri", "In Prog", "Compl", "Held", "NotStrt","Defer", "UAT"))
Held

Bob

 

[Rich]

If you'd used a look up table then you wouldn't have needed any Iifs, buts Choose etc

 

[MsLady]

Hi -
Using the Choose() function could eliminate all of those nested IIFs and make it much less prone to error. Example from the debug (immediate) window:

MinOfStatusCode = 4
? iif(MinOfStatusCode >7, "Closed", choose(MinOfStatusCode + 1, "BOR/Scope","Pri", "In Prog", "Compl", "Held", "NotStrt","Defer", "UAT"))
Held

Bob

Thanks buddy!
I learned something new. I'll try it.

 

[MsLady]

If you'd used a look up table then you wouldn't have needed any Iifs, buts Choose etc

I know. but i have soo many tables on my joins already. I had to eliminate some just to make my query run faster