By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

IIf() formula too complex to run query - runtime error

P: 6
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.
Expand|Select|Wrap|Line Numbers
  1. IIf([BegTaxBasis]=0 And [Contribution]+[Distribution]=0
  2.    ,0
  3.    ,IIf([BegTaxBasis]=0 And [TaxIncSubTotal]=0
  4.       ,-[Distribution]
  5.       ,IIf([Distribution]=0
  6.          ,0
  7.          ,IIf([TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]<[Distribution]
  8.             ,-[Distribution]
  9.             ,0
  10.             )
  11.          )
  12.       )
  13.    )
Then I updated it to, see below:
Expand|Select|Wrap|Line Numbers
  1. IIf([BegTaxBasis]=0 And [Contribution]+[Distribution]=0
  2.    ,0
  3.    ,IIf([BegTaxBasis]=0 And [TaxIncSubTotal]=0
  4.       ,-[Distribution]
  5.       ,IIf([Distribution]=0
  6.          ,0
  7.          ,IIf([TBBLL]>0
  8.             ,0
  9.             ,IIf([TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]<[Distribution]
  10.                ,-[Distribution]
  11.                ,IIf([TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]>[Distribution] 
  12.                   And [TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]<0 
  13.                   And [TaxIncSubTotal]<0
  14.                      ,[TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]-[TaxIncSubTotal]
  15.                      ,[TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]
  16.                    )
  17.                 )
  18.              )
  19.           )
  20.       )
  21.    )
And now the query wont run, any help is much appreciated, thanks!
May 12 '17 #1
Share this Question
Share on Google+
13 Replies


PhilOfWalton
Expert 100+
P: 1,430
I can't see off hand what the problem is, but a few tips which may help.

I suggest you put Nz round each of your fields.
I suggest you copy the formula to notebook (it's easier to read) then set each block of conditions on a separate line.
Check the bracketing round each line. E.G

Expand|Select|Wrap|Line Numbers
  1. IIf((Nz(TBBLL)+Nz(Recourse)+Nz(QualifiedNonrecourse)+ Nz(NonRecourse)) < Nz(Distribution) .......
  2.  
Another tip is to use the Iif clause like this

IIf(Condition, "A","B")

That will check the first IIF clause is working. If that is OK, substitute the "A" with the next genuine bit of your Iif clause. Then substitute "B". Build up slowly until it works.

So Start off with
Expand|Select|Wrap|Line Numbers
  1. IIf(Nz(BegTaxBasis) = 0 And (Nz(Contribution) + Nz(Distribution)) = 0, "A","B")
  2.  
The error message is misleading. It usually means that there is an error in your Iif statement that Access can't resolve.

Phil
May 12 '17 #2

zmbd
Expert Mod 5K+
P: 5,397
Would you post the entire SQL for the query?
You may have ran into some limitations for joins, length of SQL, length of WHERE clause etc....
(here's one such situation where this error can pop-up
Error message when you run a query in Access 2007: "System Resource Exceeded" or "Query is too complex")
May 12 '17 #3

NeoPa
Expert Mod 15k+
P: 31,418
Perhaps you'd find the whole equation simpler to express using the Switch() function instead of multiple IIf()s.
May 13 '17 #4

P: 6
here is the entire query:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblBasis.PartnershipID, tblPartnership.PartnershipName, tblPartnership.PartnershipEIN, tblBasis.TaxYearEndDate, tblBasis.Period, IIf(IsNull([tblScheduleK1].[document_status]),"Not Received",[document_status]) AS DocumentStatus, IIf(IsNull([tblScheduleK1].[DateReceived]),"Book",[tblScheduleK1].[DateReceived]) AS BookOrDateReceived, tblBasis.ConsolidatedGroupID, tblConsolidatedGroup.ConsolidatedGroupName, tblConsolidatedGroup.EIN, tblBasis.LegalEntityID, tblLegalEntity.LegalEntityName, tblLegalEntity.LegalEntityEIN, tblLegalEntity.LegalEntityOtherID, tblLegalEntity.LegalEntityType, tblLegalEntity.LegalEntityOtherDescr, tblPartnership.OtherID, IIf(Left([tblPartnership]![PartnershipEIN],1)="9" Or [tblPartnership]![ReasonNoEIN]="Foreign","Foreign",IIf(Left([tblPartnership]![PartnershipEIN],1)<>"9","Domestic",Null)) AS [Foreign/Domestic Indicator], Val(Nz([TaxBasisBeginningActual],0)) AS BegTaxBasis, Nz([tblBasis]![TaxBasisAcquisitionsMergers],0)+Nz([tblBasis]![TaxBasisTransfers],0) AS BasisTransfers, Val(Nz([TaxCapitalContributions],0)) AS Contribution, Val(Nz([TaxDistributions],0))*-1 AS Distribution, Nz([tblBasis]![TaxOrdinaryIncome],0)+Nz([tblBasis]![TaxRentalRealEstateIncome],0)+Nz([tblBasis]![TaxInterestIncome],0)+Nz([tblBasis]![TaxDividendIncome],0)+Nz([tblBasis]![TaxRoyalties],0)+Nz([tblBasis]![TaxCapitalGainLoss],0)+Nz([tblBasis]![TaxOtherIncome],0)-Nz([tblBasis]![TaxDeductions],0)-Nz([tblBasis]![TaxForeignTaxesPaidAccrued],0) AS TaxIncSubTotal, Nz([tblBasis]![TaxExemptIncome],0)-Nz([tblBasis]![TaxNonDeductibleExpenses],0) AS Perm, Nz([tblBasis]![TaxAuditAdjustments],0)+Nz([tblBasis]![TaxOtherAdjustments],0) AS OtherIncrDecr, (IIf([DocumentStatus]="5-Final",[BegTaxBasis]+[BasisTransfers]+[Contribution]+[Distribution]+[TaxIncSubTotal]+[Perm]+[OtherIncrDecr],0)+Nz([tblBasis].[TaxPTPDisposal],0))*-1 AS DispoPartInt, [BegTaxBasis]+[BasisTransfers]+[Contribution]+[Distribution]+[TaxIncSubTotal]+[Perm]+[OtherIncrDecr]+[CreditsAdjBasis]+[DispoPartInt] AS TBBLL, [QualifiedNonrecourse]+[NonRecourse]+[Recourse] AS TotalLiab, 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)))) AS GainRecDistr, [TBBLL]+[GainRecDistr] AS TBBLLaG, Nz([tblBasis]![PYAtRiskLiabUsedforLosses],0)+Nz([tblBasis]![QNRforAtRiskLimit],0)+Nz([tblBasis]![TaxRecourse],0) AS QNRaRD, [TBBLLaG]+[QualifiedNonrecourse]+[Nonrecourse]+[Recourse] AS TBBLLwLia, IIf([TaxIncSubTotal]>0,0,-1*(IIf([TBBLLwLia]<0,(IIf([TaxIncSubTotal]+[Perm]+[OtherIncrDecr]+[CreditsAdjBasis]>[TBBLLwLia],[TaxIncSubTotal],[TBBLLwLia])),0))) AS CYLossLimit, Abs(Val(Nz([tblBasis]![BegLossCarryForward],0))) AS PYLossLimit, IIf([BLCF]=0,0,IIf(([TBBLLaG]+[TotalLiab])<0,0,IIf(([TBBLL]+[BLCF])>0,IIf([BLCF]<[TaxIncSubTotal],-[BLCF],-[TaxIncSubTotal]),0))) AS CYLossLimitUtil, [CYLossLimit]+[CYLossLimitUtil] AS LossLimit, [TaxIncSubTotal]+[LossLimit] AS TIaLL, [TBBLLaG]+[LossLimit] AS EndTBaLLwoLia, [EndTBaLLwoLia]+[TotalLiab] AS TBaLLaLia, [PYLossLimit]+[CYLossLimit]+[CYLossLimitUtil] AS CumLossLimit, IIf([TBBLLaG]>0,0,IIf(-[TBBLL]<(Nz([tblBasis]![QNRforAtRiskLimit],0)+Nz([tblBasis]![TaxRecourse],0)),-[TBBLL],Nz([tblBasis]![QNRforAtRiskLimit],0)+Nz([tblBasis]![TaxRecourse],0))) AS CYatRiskLiaUsed, tblBasis.TaxRemovalofBasis, tblBasis.ProceedsSaleOrDispoPartnership, Val(Nz([tblBasis].[TaxPTPOrdinaryAdj],0)) AS TaxOrdGainDispo, Abs(Val(Nz([tblBasis]![TaxPTPDisposal],0)))*-1 AS PTPDisposal, Nz([tblBasis]![ProceedsSaleOrDispoPartnership],0)+[DispoPartInt] AS GainLossFrDispo, tblBasis.TaxPTPAdjustments, Abs(Val(Nz([tblBasis]![BegLossCarryForward],0))) AS BLCF, Abs(Nz([tblBasis]![PYAtRiskLiabUsedforLosses],0))*-1 AS PYatRisk, tblBasis.QNRforAtRiskLimit, Val(Nz([tblBasis]![TaxCreditsAdjBasis],0)) AS CreditsAdjBasis, tblBasis.TaxCreditsAdjBasisDescr, IIf([tblScheduleK1]![document_status]="5-Final",0,Nz([tblBasis]![TaxRecourse],0)) AS Recourse, IIf([tblScheduleK1]![document_status]="5-Final",0,Nz([tblBasis]![TaxQualifiedNonrecourse],0)) AS QualifiedNonrecourse, Val(Nz([tblBasis]![TaxNonrecourse],0)) AS NonRecourse, IIf([TaxIncSubTotal]>=0,0,IIf([TotalLiab]=0,0,IIf([BegTaxBasis]>0,0,IIf(([PYatRisk]+[TotalLiab])<([Contribution]+[Distribution]+[TaxIncSubTotal]+[Perm]+[OtherIncrDecr]+[CreditsAdjBasis]+[DispoPartInt]),0,IIf(([PYatRisk]+[TotalLiab])>([Contribution]+[Distribution]+[TaxIncSubTotal]+[Perm]+[OtherIncrDecr]+[CreditsAdjBasis]+[DispoPartInt]),IIf(([PYatRisk]+[TotalLiab])<-([Distribution]+[TaxIncSubTotal]+[Perm]+[OtherIncrDecr]+jo[CreditsAdjBasis]),-([PYatRisk]+[TotalLiab]),([Distribution]+[TaxIncSubTotal]+[Perm]+[OtherIncrDecr]+[CreditsAdjBasis]))))))) AS CYLiaUsedforLosses, IIf(([PYatRisk]+[TotalLiab]+[CYLiaUsedforLosses])>0,0,([PYatRisk]+[TotalLiab]+[CYLiaUsedforLosses])) AS LossTakenExLia, IIf([DocumentStatus]="5-Final",[GainLossFrDispo]-Nz([tblBasis]![OrdinaryGainLossFromDispo],0)-Val(Nz([tblBasis].[TaxPTPOrdinaryAdj],0)),Nz([tblBasis].[TaxPTPAdjustments],0)) AS TaxCapitalGainLossFromDispo
  2. FROM ((tblLegalEntity RIGHT JOIN (tblConsolidatedGroup RIGHT JOIN tblBasis ON tblConsolidatedGroup.ConsolidatedGroupID = tblBasis.ConsolidatedGroupID) ON tblLegalEntity.LegalEntityID = tblBasis.LegalEntityID) LEFT JOIN tblPartnership ON tblBasis.PartnershipID = tblPartnership.PartnershipID) LEFT JOIN tblScheduleK1 ON (tblBasis.ConsolidatedGroupID = tblScheduleK1.ConsolidatedGroupID) AND (tblBasis.LegalEntityID = tblScheduleK1.LegalEntityID) AND (tblBasis.PartnershipID = tblScheduleK1.PartnershipID) AND (tblBasis.TaxYearEndDate = tblScheduleK1.TaxYearEndDate) AND (tblBasis.Period = tblScheduleK1.Period) AND (tblBasis.K1UsedforBasis = tblScheduleK1.K1UsedforBasis)
  3. WHERE (((tblBasis.Period)<>0));
May 16 '17 #5

zmbd
Expert Mod 5K+
P: 5,397
That's a hydra of an SQL... it may take us a bit to work through the script (just under 6000 characters)

Out of curiously, how many fields are in there?

I'm also getting a missing operator error somewhere in the line IIF([TaxincSubTotal]>=0,IIF(... flagging on +[OtherIncrDecr]+jo[CreditsAdjBasis]),-([PYatRisk]+[TotalLiab]),([Distribution]+; however, that could be a symptom of not having your tables in my test database.
May 17 '17 #6

P: 6
57 haha, so I know the problem is with this section in particular because the rest of it runs perfectly:
Expand|Select|Wrap|Line Numbers
  1. IIf([TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]>[Distribution] And [TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]<0 And [TaxIncSubTotal]<0,[TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]-[TaxIncSubTotal],[TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]))))))
How could I convert the above into VBA? I'm thinking that may be my best bet at this point.
May 23 '17 #7

PhilOfWalton
Expert 100+
P: 1,430
I'm not surprised that there is a problem, because, apart from anything else, you did not use the Nz function I suggested, you did not use brackets round each part of your statement, and most obviously, there is 1 left hand bracket and 6 right hand brackets. They must always be in pairs.

Phil
May 23 '17 #8

zmbd
Expert Mod 5K+
P: 5,397
I'm not surprised that there is a problem, because, apart from anything else, you did not use the Nz function I suggested, you did not use brackets round each part of your statement, and most obviously, there is 1 left hand bracket and 6 right hand brackets. They must always be in pairs.
Saw the brackets as mis-matched but figured that was a typo with such a very large string.

NZ() is a VBA code function and although I use it there can be some issues.
http://allenbrowne.com/QueryPerfIssue.html
May 24 '17 #9

NeoPa
Expert Mod 15k+
P: 31,418
Nevertheless, handling of possible Nulls is still critical. There are better ways and worse ways, but no way is likely to lead to trouble. It's a point that needs to be addressed.
May 25 '17 #10

P: 6
I tried the NZ function in the access expression however still said query is too complex. I began to convert it to VBA:
Expand|Select|Wrap|Line Numbers
  1. Public Function GetVal(BegTaxBasis As Variant, Contribution As Variant, Distribution As Variant, TaxIncSubTotal As Variant, TBBLL As Variant, Recourse As Variant, QualifiedNonRecourse As Variant, NonRecourse As Variant) As Long
  2.  
  3.   Dim TRQN As Long
  4.   TQRN = TBBLL + Recourse + QualifiedNonRecourse + NonRecourse
  5.   If BegTaxBasis = 0 And TaxIncSubTotal = 0 Or TRQN < Distribution Then
  6.      GetVal = -Distribution
  7.   ElseIf (TRQN > Distribution And TRQN < 0) And TaxIncSub < 0 Then
  8.      GetVal = TRQN - TaxIncSub
  9.   Else
  10.      GetVal = TRQN
  11.   End If
  12. End Function
However, now am getting "enter parameter value" message, when I run the query and it doesn't calculate.
May 26 '17 #11

PhilOfWalton
Expert 100+
P: 1,430
I suspect that you have not got

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
At the top of your module. If you had, you would find that the module would not complile.

Why? The first line refers to TRQN
The second tine refers to TQRN

All modules should be headed with
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
The Option Explicit ensures that when you make a reference to a variable, that "thing" exists (It could be something following Dim or Const or the name of a field on a form)

In addition your code has not used the Nz() function as numerous people have suggested.

Phil
May 26 '17 #12

NeoPa
Expert Mod 15k+
P: 31,418
Please check out the posting instructions at How to ask "good" questions -- READ BEFORE SUBMITTING A QUESTION!.

With the embedded links it explains a number of concepts that will help you to post in such a way that will make your and our lives easier.

Please understand these guidelines are not optional. That said, if you struggle with any of them please let us know (You can always PM me directly on site usage issues.) and we'll be happy to help.

@Experts and other members.
Moderators should already know this but it is perfectly acceptable to post a link to the thread above. Otherwise, issuing advice and guidance to other members should be left to Moderators and above.
May 26 '17 #13

ADezii
Expert 5K+
P: 8,623
Aside from all that has been said, there apparently is a mismatch between the TaxIncSubTotal Argument in the Function Declaration and the reference to TaxIncSub in the If...Else If...Else...End If Construct.
Expand|Select|Wrap|Line Numbers
  1. Public Function GetVal(BegTaxBasis As Variant, Contribution As Variant, Distribution As Variant, TaxIncSubTotal As Variant, _
  2.                        TBBLL As Variant, Recourse As Variant, QualifiedNonRecourse As Variant, NonRecourse As Variant) As Long
  3. Dim TRQN As Long
  4.  
  5. TRQN = TBBLL + Recourse + QualifiedNonRecourse + NonRecourse
  6.  
  7. If BegTaxBasis = 0 And TaxIncSubTotal = 0 Or TRQN < Distribution Then
  8.   GetVal = -Distribution
  9. ElseIf (TRQN > Distribution And TRQN < 0) And TaxIncSub < 0 Then
  10.   GetVal = TRQN - TaxIncSub
  11. Else
  12.   GetVal = TRQN
  13. End If
  14. End Function
May 28 '17 #14

Post your reply

Sign in to post your reply or Sign up for a free account.