Limit on embedded IIF statement in query

[Angel69]

Hi,

I have the following code

Gap: IIf([Q1]=2,"GAP",IIf([Q2]=2,"GAP",IIf([Q3]=2,"GAP",IIf([Q4]=2,"GAP",IIf([Q5]=2,"GAP",IIf([Q6]=2,"GAP",IIf([Q7]=2,"GAP",IIf([Q8]=2,"GAP",IIf([Q9]=2,"GAP",IIf([Q10]=2,"GAP",IIf([Q11]=2,"GAP",IIf([Q12]=2,"GAP",IIf([Q13]=2,"GAP",IIf([Q14]=2,"GAP",""))))))))))))))

I need to add Q15 and Q16 but there appears to be a limit on how many IIf statements I can embed and I get an error that my formula is too complex. Any ideas how to get around it?

Thanks.

 

[plog]

In my world, you get to nest 2 iff statements. After that its time for a vba function. However, I think you might have larger problems: numerated field names are a sign of a poor structure.

Why do you have so many Q fields? What do they represent? In plain english, explain to a 3rd grader what this system is to do and what the point of your expression is.

 

[Ari]

Hi

If it is not possible to change the structure of your query, I suggest using the switch() function, which solves the limitation of fields.

example:

switch([q1]=2;"GAP";[q2]=2;"GAP";[q3]=2;"GAP";...;[q17]=2;"GAP")

 

[stopher]

I agree with the other posts about design.

Nevertheless you could also do (less typing):

IIF( [q1]=2 OR [q2]=2 OR [q3]=2 OR... OR [q17]=2,"GAP","")

Or even less typing:

IIF (2 IN([q1], [q2], [q3], ..... , [17]), "GAP", "")

Chris