0

I have a query in Access Database and when I try to run it, I get an error message "query is too complex to run." Runtime error 3360." The problem is with one formula / programming logic, see below:

Previously the formula was the below - and it worked. 

IIf([BegTaxBasis]=0 And [Contribution]+[Distribution]=0,
  0,
  IIf([BegTaxBasis]=0 And [TaxIncSubTotal]=0,
     -[Distribution],
     IIf([Distribution]=0,
         0,
         IIf([TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]<[Distribution],
             -[Distribution],
             0))))

Then I updated it to, see below: 

IIf([BegTaxBasis]=0 And [Contribution]+[Distribution]=0,
  0,
  IIf([BegTaxBasis]=0 And [TaxIncSubTotal]=0,
      -[Distribution],
      IIf([Distribution]=0,
         0,
         IIf([TBBLL]>0,
            0,
            IIf([TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]<[Distribution],
               -[Distribution],
               IIf([TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]>[Distribution] And [TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]<0 And [TaxIncSubTotal]<0,
                  [TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]-[TaxIncSubTotal],
                  [TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse])))))

And now the query wont run, any help is much appreciated, thanks!

C Perkins
  • 3,733
  • 4
  • 23
  • 37
Clarisa
  • 3
  • 5
  • Length of expression is 540, limit is 1024 for any cell in query design, so that's not the issue. Sorry, I can't see anything wrong with the expression. Try adding the IIf clauses one at a time until it fails. – June7 May 12 '17 at 03:10
  • any advice on how to break up the IF statements, use another formula in access expression? – Clarisa May 12 '17 at 17:40
  • This may be a duplicate of [Query too complex](http://stackoverflow.com/questions/12088312/query-too-complex) and its answers might be helpful. A search on "query too complex [ms-access]" returns too many questions to analyze now, but you might have found your answer with some additional research. Try that next time. In the mean time, I hope my answer is helpful and more simple that what I saw in a quick look-see. – C Perkins May 12 '17 at 18:35

1 Answers1

0

You could try assigning intermediate values to a separate field or fields, then reference such fields in the final expression. This will only work for certain queries and SQL clauses, but you didn't give a context for your formula so I can't comment more on your particular code. These type of intermediate field values cannot be referenced in JOIN, ORDER BY or WHERE clauses--only in other SELECT fields. The order of the fields is important... the field must be defined before referencing it later in the SQL statement.

This facilitates re-use of duplicate expression, e.g. [TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse], so it can shorten the overall query.

For example:

SELECT ([TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]>[Distribution] 
        And [TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]<0 
        And [TaxIncSubTotal]<0) As Condition1,
   ([TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]-[TaxIncSubTotal]) As TrueValue1,
   ([TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]) As FalseValue1, 
   Iif([Condition1],[TrueValue1],[FalseValue1]) As FalseValue2,
   ...
   [Some final calculation using previous fields] As FinalValue

A similar technique is to calculate some values in another saved or embedded query then join to that query and reference the partial calculations for the final expression. This technique can overcome the limitations of defining fields in the same select query. For instance, this would allow using intermediate calculated values in a join expression, whereas the first technique would not allow that. Using a series of saved, joined queries would circumvent single-query length limitations as mentioned by June7.

C Perkins
  • 3,733
  • 4
  • 23
  • 37
  • Thank you ^^ so I found out only this section is causing the problem. is there any way to convert this into VBA?? IIf([TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]>[Distribution] And [TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]<0 And [TaxIncSubTotal]<0,[TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]-[TaxIncSubTotal],[TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse])))))) – Clarisa May 23 '17 at 19:54
  • Short answer is yes. But this is really a different question which you should search for and attempt to answer separately. I think you should be able to find plenty of help by searching here and on the web. I did one quick search a found this link [How to call VBA-function from inside sql-query?](https://stackoverflow.com/questions/2261090/how-to-call-vba-function-from-inside-sql-query) – C Perkins May 23 '17 at 20:12
  • But your long statement indicates that you did not split it up according to pattern in my answer, or at least it appears that way. Is there anything you don't understand about my answer that I could clarify? Or did something not work as you expected? Nothing is wrong with learning to write and call VBA functions from the query, but depending on the size of your data sets, it may or may not be as efficient. – C Perkins May 23 '17 at 20:16
  • For what it's worth, I just added a better answer to the question that I [previously linked to](https://stackoverflow.com/questions/2261090/how-to-call-vba-function-from-inside-sql-query). Although the specific topic was TRANSFORM queries, you can see how I defined a VBA function and called it from the WHERE clause in the query. You could define a function and pass in multiple table fields, for example: `YourFunction([TBBLL], [Recourse], [QualifiedNonrecourse], [NonRecourse])`. I suggest more than one function that breaks down your logic which could then be called from various contexts. – C Perkins May 23 '17 at 23:03