Nested IF Statements

Posted on 2011-04-20
Medium Priority
Last Modified: 2013-11-05
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')
Question by:Lulu0
1 Comment
LVL 13

Accepted Solution

AielloJ earned 2000 total points
ID: 35434908

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.


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question