Access Query is too complex to run - error message

[Tia77]

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!

 

[LaurieHamlin]

Tia,
I haven't tried running this so it still may not work, but I simplified it a little and added () to clarify.

IIf(([BegTaxBasis]=0 And [Contribution]+[Distribution]=0) or ([BegTaxBasis]=0 And [Contribution]+[Distribution]=0) Or [Distribution]=0 Or [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])))

I hope that helps.
Laurie

 

[Tia77]

Thanks for your help!

When I try to run that it gives me a compile error: see image attached.

 

[dhookom]

Create a user-defined function that accepts the various fields and returns the calculated value. Add comments and save it in a module named modBusinessCalcs.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[Tia77]

I began to convert it to VBA:

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

Dim TRQN As Long
TQRN = TBBLL + Recourse + QualifiedNonRecourse + NonRecourse
If BegTaxBasis = 0 And TaxIncSubTotal = 0 Or TRQN < Distribution Then
GetVal = -Distribution
ElseIf (TRQN > Distribution And TRQN < 0) And TaxIncSub < 0 Then
GetVal = TRQN - TaxIncSub
Else
GetVal = TRQN
End If
End Function

However, now am getting "enter parameter value" message, when I run the query and it doesn't calculate.

 

[dhookom]

Tia77,
Is this the same expression that MajP already provided a solution?

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016