?#?#?#?# Aggregate error

[texasalynn]

I have this query but can not get LIT Description field to work. I have looked it up and down, inside and out but can not find where it is not liking it?

SELECT [Customer Invoice].[G/L ACCT#], Sum([Customer Invoice].[SUBTOTAL AMOUNT]) AS [SumOfSUBTOTAL AMOUNT], Sum([Customer Invoice].[TAX AMOUNT]) AS [SumOfTAX AMOUNT], Sum([Customer Invoice].[TOTAL AMOUNT]) AS [SumOfTOTAL AMOUNT], IIf([type]="Recycle","CARDBOARD REV","# OF HAULS=" & Count([HDIDDT])) AS [LIT Descripiton], [Customer Invoice].[LOCATION/STORE#], [Customer Invoice].[LOCATION ADDRESS]
FROM [Customer Invoice]
GROUP BY [Customer Invoice].[G/L ACCT#], [Customer Invoice].[LOCATION/STORE#], [Customer Invoice].[LOCATION ADDRESS];

Can someone please direct me

 

[Norie]

What's the full actual error message?

 

[texasalynn]

"you tried to execute a query that does not include the specified expression IIf([type]="Recycle","CARDBOARD REV","# OF HAULS=" & Count([HDIDDT])) as part of an aggregate function"

 

[Andrew Fergus]

Hi Lynn
I haven't tested this so I'm not sure if it will work but try changing 'Group By' to 'Expression' under the LIT Description field.
HTH, Andrew

 

[texasalynn]

Yes I had that and that's what gives the error. I know it's because of the group by but I don't know why it is not working. Any other thoughts?

 

[Andrew Fergus]

Hi Lynn

I still haven't tested this (in other words I haven't replicated the problem) but how about splitting the text and numerical values into 2 different columns? If the type = Recycle then you could set the value to 0, instead of doing the count. Does it make any difference?

Just a thought
Andrew

 

[CoderX]

That Iif function coupled with a Count function can't work in an aggregate query. YOu may try using a subquery to obtain the count.