Nested IIf statements

[bosnich]

Is there a limit on the number of nested IIf statements that Access 97 will accept? I am getting a "formula too complex" message, or Access just does not accept any statements after about 15.
The following is the beginning of the statement I am having a problem with.


=IIf([Type]="PLUGGED" And [DIA]="1.5",100,IIf([Type]="plugged" And [dia]="2",50,IIf

 

[PaulF]

why not create a function to get your result.
In a Module Add something along this line

Public Function ResultIs(strType as string, dblDia as Double)
Select Case strType
Case "Plugged"
Select Case dblDia
case 1.5
ResultIs = "100"
case 2.0
ResultIs = ........
case ...... ' Continue with Options
case Else
ResultIs = ......
End Select
Case "SomethingElse"
Select Case 'Continue wth Options
.......
End Select
Case Else
ResultIs = "UnknownItem"
End Select
End Function

Then in the Query use for the FieldName

ExpressionName: ResultIs([Type],[Dia])



PaulF

 

[Craig0201]

The limit is 8.....same as Excel I believe.....

Craig

 

[MichaelRed]

None of my docs. have any notation re a (machine/software) limit on the # of levels for nesting this function.

I just tried this one, which is twelve deep, and it works "Just Fine".

On a pratical level, the limit for me is about three, wheather it is my eyes or my brain, when I get above three, it takes longer to count the (*&&^%^%$#&(* parens than to pop open a module and code it - like Pauls suggestion.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over