Expression is too complex

[legendv]

Anyone know how to handle this?

I have a query that using the iif function with 9 conditions listed - it works, but when I add the 10th condition it gives me the expression is too complex error msg. Anyone know how to get around this?

 

[KyleS]

The first thing that comes to mind would be to do it as a function in a Module. Call it

"Function QueryIF()"

so if you're returning a string your function would look something like this:
Function QueryIF() As String

If ..... Then
QueryIF = "Something"
ElseIf ..... Then
QueryIF = "Something Else"
etc...
End If

End Function

Or you could use a Case statement which would run faster:

Function QueryIF() As String

Select Case x
Case = .....
QueryIF = "Something"
Case = ......1
QueryIF = "Something Else"
etc...
End Select

End Function

Then replace the IIF function in your query with QueryIF() Kyle

 

[dalchri]

Yeah, it sounds like you're after VB select case functionality. There is an access query equivalent that you can use directly in the query. You might want to try the Switch function. Here is an example that translates one character codes in a database field into "english" words for display on a report:
[tt]
SELECT Switch(Method='P','Percent',
Method='F','Flat Rate',
Method='A','Additional Amount',
True,'Unrecognized Code') AS EnglishMethod
FROM ExampleTable
[/tt]