How many if statements can be nested together?

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I'm trying to create an expression in an Access query but
I keep getting a message saying that the query is too
complex. I have 18 if statements nested together.

Can anyone help me out?

Thanks.
 
How about providing a sample of what you are working with? When I see
multiple nested IIf()s, I have a tendency to assume the table structure
isn't normalized.
 
I'm trying to create an expression in an Access query but
I keep getting a message saying that the query is too
complex. I have 18 if statements nested together.
Click to expand...

The limit isn't specifically on the number of IIF's, it's on the total
compiled length of the query and on the number of bytes in any single
expression.

18 nested IIF's is WAY too complex for me to get my head around! I'd
suggest either replacing the whole gemillah with a single Switch()
function call, or (better) reconsidering your table design; a join to
a lookup table may be a much simpler solution.
 
Have you tried using SQL-specific subqueries? What exactly
do you need so many if statements for? Let me know and
hopefully I'll be able to help.
 
You probably exceed the limit of 1024 characters in a cell
in the Query Grid. Also, I think you meant the nested IIf
() function, NOT If statement.

I would even try with 3 nested levels. Write a UDF (User-
Defined Function) using If statements and then use the UDF
in your Query.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top