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

M

Mohsin Malik

Registered User.

Local time

Today, 17:52

Joined

Mar 25, 2012

Messages

175

• Feb 3, 2013

• #1

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

 

J

JHB

Have been here a while

Local time

Today, 15:52

Joined

Jun 17, 2012

Messages

7,732

• Feb 4, 2013

• #2

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

 

M

Mohsin Malik

Registered User.

Local time

Today, 17:52

Joined

Mar 25, 2012

Messages

175

• Feb 4, 2013

• #3

JHB said:

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

Click to expand...

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)))))))))))))))))

 

J

JHB

Have been here a while

Local time

Today, 15:52

Joined

Jun 17, 2012

Messages

7,732

• Feb 4, 2013

• #4

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.