• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Nested IF Statements

I'm unable to execute the query below. Is there a limit on nested IF's? I get the error "Expression is too complex".

iif(Delivery_Status = ' ' or Delivery_Status is null,'Missing Data'
      ,iif(Expected_Date < Ready_Date,'Bad Settings'
            ,iif(Create_Date > Order_Date,'Bad Entry'
                  ,iif((abs(actual_days - sys_days)>1)
                        ,iif(Loc <> Primary_Loc,'Bad Loc'
                              ,iif(used_type <> Primary_Type,'Bad Type'
                                    ,iif(hours>48,'Too long'
                                          ,iif(Actual_Pickup_Date <> Sched_Pickup_Date and Actual_Pickup_Date is not null and Sched_Pickup_Date is not null
                                                ,iif(Actual_Pickup_Date < Sched_Pickup_Date,'Early Pickup'
                                                      ,'Late Pickup'
                                                )
                                                ,iif((actual_days - sys_days)<-1, 'Fast Transit'
                                                      ,iif((actual_days - sys_days)>1,'Slow Transit','Unknown')
                                                )
                                          )
                                    )
                              )
                        )
                        ,iif(actual_days < sys_days,'Fast Transit'
                              ,iif(actual_days > sys_days, 'Slow Transit','Unknown')
                        )
                  )
            )
      )
);
0
Lulu0
Asked:
Lulu0
1 Solution
 
AielloJCommented:
Lulu0:

Most, if not all, versions of MS-Access have a nesting limit of 7.  The limit also applies to some other operations like function call nesting.

I would suggest taking a look at the logic and see if it can be simplified.

AielloJ
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now