Expression Too Complex?!

[djmousie]

I've been adding new variables to my IF then statement as I need to, however, when I went to add my 15th nested IF THEN statement, I got an error msg stating "Expression Too Complex"?! Is there a way for me to avoid getting this? Or away around this? And does Access have a limit?

 

[lespaul]

There is a limit on the number of characters in the query. The easiest way to reduce the length of your query is to use an alias for the table name. For instance, Access might build the query like:

SELECT ReallyLongTableName.Field1, ReallyLongTableName.Field2, ReallyLongTableName2.Field2
FROM ReallyLongTableName, ReallyLongTableName2
INNER JOIN ReallyLongTableName on ReallyLongTableName2.Field6 = ReallyLongTableName.Field1
WHERE ReallyLongTableName.Field1 = 'Something'
AND ReallyLongTableName.Field2 = 'Nothing'
Group By ReallyLongTableName.Field3
Order By ReallyLongTableName.Field4

Which can be written with an alias

SELECT A.Field1, A.Field2, B.Field2
FROM ReallyLongTableName AS A, ReallyLongTableName2 AS B
WHERE A.Field1 = 'Something'
AND B.Field2 = 'Nothing'
Group By A.Field3
Order By A.Field4

You may also be better off with a CASE statement (I forget if Access can do it or not!!) instead of a bunch of Nested IIF statements (especially if you are up to 15 of them!!)

Are your tables normalized? Why do you need a 15 deep nested if statement?

HTH

Leslie

 

[BNPMike]

djmousie

Where have you got these If statements - VBA presumably?

 

[dhookom]

djmousie,
I expect that you may be hard-coding expressions rather than using a lookup table to perform comparisons. Application should be data-driven if possible. However, since you didn't provide very much information (sql statement for instance), it is difficult to tell.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.