Expression too complex in query expression

RubberChicken

Registered User.
Local time
Today, 13:29
Joined
Jun 24, 2013
Messages
18
Hi Guys,

I am hoping someone can look at the code below and tell my why I am getting this error?

I have added
IIf([CustGrp]='P3','Mid Market',
and the respective ) to the code and now I am getting this error.

Thanks for your help.

MyDB.Execute "INSERT INTO tblTransactionsOpera ( Division, CustomerGroup, CustomerRef, Customername, " _
& "TransactionNo, TransactionDate, TransactionType, Amount, Ledger, Periodend, InDispute, OriginalAmount ) " _
& "SELECT IIf([CustGrp]='CA','Small Business',IIf([CustGrp] Like 'D*','Accident Services',IIf([CustGrp]= 'J2','Garage Network'," _
& "IIf([CustGrp]= 'J3','Battery Sales',IIf([CustGrp]='P2','SME Fleet',IIf([CustGrp]='P3','Mid Market',IIf([CustGrp]='P5','Agresso Strategic',IIf([CustGrp]='B1','Risk Management'," _
& "IIf([CustGrp]='PA' Or [CustGrp]='PE','Insurance pay On Use',IIf([CustGrp]='R1','Ad customer services'," _
& "IIf([CustGrp]='T2','Commercial Training',IIf([CustGrp]= 'L1','InsurerA',IIf([CustGrp]= 'L2','InsurerB',))))))))))))) " _
& "AS Segment, " _
& "Agresso.CustGrp, Agresso.CustID, " _
& "Agresso.Customername, Agresso.InvoiceNo, Agresso.InvoiceDate, " _
& "IIf([TT]='AR','Accounts Receivable Invoice', IIf([TT]='CA','Cash Allocation', " _
& "IIf([TT]='CN','Credit Note Allocated',IIf([TT]='CR','Cash Received',IIf([TT]='BI','Other Invoices'," _
& "IIf([TT]='GL','General Ledger',IIf([TT]='SF','Siebel Fleet Invoices',IIf([TT]='SO','Other Invoices'," _
& "IIf([TT]='LC','Legal Credit Note',IIf([TT]='RR','Reversal',IIf([TT]='BP','Bank Payments GL'," _
& "IIf([TT]='XR','AS Customer Invoices',IIf([TT]='SA','Sale Of Fixed Assets',IIf([TT]='AD','AS DD Receipts', " _
& "'Not Known')))))))))))))) AS TransactionType, Agresso.Amount, " _
& "'Agresso' AS Ledger, #" & sPeriodEnd & "# As PeriodEnd, IIf([CC] Is Not Null,True,False) AS InDispute, " _
& "Agresso.Original " _
& "FROM Agresso " _
& "WHERE (((IIf([CustGrp]='CA','Small Business',IIf([CustGrp] Like 'D*','Accident Services',IIf([CustGrp]= 'J2','Garage Network'," _
& "IIf([CustGrp]= 'J3','Battery Sales',IIf([CustGrp]='P2','SME Fleet',IIf([CustGrp]='P3','Mid Market',IIf([CustGrp]='P5','Agresso Strategic',IIf([CustGrp]='B1','Risk Management'," _
& "IIf([CustGrp]='PA' Or [CustGrp]='PE','Insurance pay On Use',IIf([CustGrp]='R1','Ad customer services'," _
& "IIf([CustGrp]='T2','Commercial Training',IIf([CustGrp]= 'L1','InsurerA',IIf([CustGrp]= 'L2','InsurerB',)))))))))))))) Is Not Null)) " _
& "And Agresso.InvoiceDate <= #" & sPeriodEnd & "# " _
& "And Agresso.Amount <> 0 " _
& "ORDER BY Agresso.CustGrp;"
 
Last edited:
You added the closing bracket, but have you added the corresponding False part for the statement? If the IIF's go beyond three deeps it normally confuses me, so I would look into Functions..
 
IIf([CustGrp]='CA','Small Business',IIf([CustGrp] Like 'D*','Accident Services',IIf([CustGrp]= 'J2','Garage Network'," _
& "IIf([CustGrp]= 'J3','Battery Sales',IIf([CustGrp]='P2','SME Fleet',IIf([CustGrp]='P3','Mid Market',IIf([CustGrp]='P5','Agresso Strategic',IIf([CustGrp]='B1','Risk Management'," _
& "IIf([CustGrp]='PA' Or [CustGrp]='PE','Insurance pay On Use',IIf([CustGrp]='R1','Audi customer services'," _
& "IIf([CustGrp]='T2','Commercial Training',IIf([CustGrp]= 'L1','InsurerA',IIf([CustGrp]= 'L2','InsurerB',))))))))))))) " _
& "AS Segment, " _

You do realize that you can create tables in Access to store data? Right?

Failing that, a CASE statement (http://www.techonthenet.com/access/functions/advanced/case.php) would be a better method to compile that abomination.

No matter the method you choose, it would probably be best to compile the SQL in a string outside of the Execute call. That way, in situations like this where it fails, you can spit out the SQL and see exactly what is being used.

But seriously though, put that data in a table and JOIN it.
 
Last edited:
Thanks for replying.

I am new to Access and this is a database that I inherited.

From what I can make out, this data is being copied to the 'tblTransactionOpera' table from a linked spreadsheet.

A new CustGrp has been added to the data on the spreadsheet and now I am left with the task of trying to get it into the database. It is a mommoth task and this is the tip of the iceberg.

Does that help any about me need to update this with very limited Access/ programming knowledge?
 
I suspect that you have passed the maximum nested IIF limit, you could try Switch but I agree with Plog's approach as being more maintainablefor future alterations.

Brian
 

Users who are viewing this thread

Back
Top Bottom