Crosstab Queries: The Expression you entered is too complex (1 Viewer)

Mohsin Malik

Registered User.
Local time
Today, 13:14
Joined
Mar 25, 2012
Messages
179
I have a crosstab Query in Ms-Access where i have set the Value Property based on Combo Box Selection, below is the Expression Builder code that worked fine, but when i want to add 03 more Criteria, It give me the following error? The Expression you entered is too complex??

This Works fine:
Value: IIf([Forms]![A]!=777,Round(Sum([cost]),0),IIf([Forms]![A]!=666,Count([ID]),IIf([Forms]![A]!=888,Round(Max([Mileage]),0),IIf([Forms]![A]!=1,Round(Avg([KM]),1),IIf([Forms]![A]!=2,Round(Avg([MPG]),1),IIf([Forms]![A]!=333,Round(Sum([Trip]),0),IIf([Forms]![A]!=4,Round(Sum([gallons]),0),IIf([Forms]![A]!=6,Round(Sum([WHT]),0),IIf([Forms]![A]!=7,Round(Sum([OtherThanWH]),0),IIf([Forms]![A]!=8,Round(Sum([insuredvalue]),0),IIf([Forms]![A]!=9,Round(Avg([CPM]),0),IIf([Forms]![A]!=10,Round(Avg([CPK]),0),IIf([Forms]![A]!=11,Round(Max([k1]),0),IIf([Forms]![A]!=12,Max([m1]),0))))))))))))))


Giving Error of Expression is too complex!

Value: IIf([Forms]![A]!=777,Round(Sum([cost]),0),IIf([Forms]![A]!=666,Count([ID]),IIf([Forms]![A]!=888,Round(Max([Mileage]),0),IIf([Forms]![A]!=1,Round(Avg([KM]),1),IIf([Forms]![A]!=2,Round(Avg([MPG]),1),IIf([Forms]![A]!=333,Round(Sum([Trip]),0),IIf([Forms]![A]!=4,Round(Sum([gallons]),0),IIf([Forms]![A]!=6,Round(Sum([WHT]),0),IIf([Forms]![A]!=7,Round(Sum([OtherThanWH]),0),IIf([Forms]![A]!=8,Round(Sum([insuredvalue]),0),IIf([Forms]![A]!=9,Round(Avg([CPM]),0),IIf([Forms]![A]!=10,Round(Avg([CPK]),0),IIf([Forms]![A]!=11,Round(Max([k1]),0),IIf([Forms]![A]!=12,Max([m1]),IIf([Forms]![A]!=191,Max([TActual]),IIf([Forms]![A]!=192,Max([TKM]),IIf([Forms]![A]!=193,Max([TMiles]),0)))))))))))))))))


Looking forward for the solution

Thank you
Mohsin
 
You are missing the :
IIf([Forms]![A]!=193
Correct is:

IIf([Forms]![A]!=193
 
You are missing the :
IIf([Forms]![A]!=193
Correct is:

IIf([Forms]![A]!=193

Thank you for highlighting the mistake but i have tried this, it is giving me the same error, please help me why it is giving me the error of The Expression you entered is too complex!

Value: IIf([Forms]![A]!=777,Round(Sum([cost]),0),IIf([Forms]![A]!=666,Count([ID]),IIf([Forms]![A]!=888,Round(Max([Mileage]),0),IIf([Forms]![A]!=1,Round(Avg([KM]),1),IIf([Forms]![A]!=2,Round(Avg([MPG]),1),IIf([Forms]![A]!=333,Round(Sum([Trip]),0),IIf([Forms]![A]!=4,Round(Sum([gallons]),0),IIf([Forms]![A]!=6,Round(Sum([WHT]),0),IIf([Forms]![A]!=7,Round(Sum([OtherThanWH]),0),IIf([Forms]![A]!=191,Round(Max([TKM]),0),IIf([Forms]![A]!=9,Round(Avg([CPM]),0),IIf([Forms]![A]!=10,Round(Avg([CPK]),0),IIf([Forms]![A]!=11,Round(Max([k1]),0),IIf([Forms]![A]!=12,Max([m1]),IIf([Forms]![A]!=191,Max([TActual]),IIf([Forms]![A]!=192,Max([TKM]),0)))))))))))))))))
 
And where did you put IIf([Forms]![A]!=193 in?
I can't see it.
If you removed it, did you then remove one ")"?

A good point to start is to take the query the works fine, and then added one iif per time. Run it, see if it works ok, if yes, then add one more iif, run it again and so on.
 

Users who are viewing this thread

Back
Top Bottom