Cannot have aggregate function in expression

[D Willett]

Hi Guys.

Could do with another set of eyes on this expression, where am I going wrong?

Tot Parts: IIf([Credited]>0,(Sum(Nz([qryPartsInv.ItmValue]-[Credited],(Sum(Nz([qryPartsInv.ItmValue])))))))

[HansV]

You can't mix a single field value (Credited) with an aggregate function (Sum), unless you group by the single field (Credited) in a Totals query.

Perhaps you can use

TotParts: DSum("ItmValue","qryPartsInv")-IIf([Credited]>0,[Credited],0)

[D Willett]

Thanks Hans
The DSum didn't throw any errors but it has set the value of 'Tot Parts' the same throughout the results.

ScreenHunter_112.jpg

The expression should look at the value of [Credited] and if it is greater than zero set the value of parts [Tot Parts] to zero. Maybe the AQL will help.

SELECT tblJCV.WorkProviderName AS Type, Count(tblJCV.JobID) AS Volume, Sum(tblJCV.TotalInvoicednet) AS [Tot Nett], Sum(tblJCV.AllocHours) AS [Tot Hrs], Sum(Nz([qryLabInv.ItmValue])) AS [Tot Labour], IIf([Credited]>0,(Sum(Nz([qryPartsInv.ItmValue]-[Credited],(Sum(Nz([qryPartsInv.ItmValue]))))))) AS [Tot Parts], Sum(Nz([qryMatsInv.ItmValue])) AS [Tot Mats], Sum(Nz([qryOtherInv.ItmValue])) AS [Tot Other], tblJCV.JobID, tblJCV.WriteOffDate, Sum(Nz([qryLabInv.ItmNett])) AS [Tot Labour Nett], Sum(IIf(Nz([qryPartsInv.ItmNett])>Nz([qryPartsInv.ItmValue]),Nz([qryPartsInv.ItmValue]),Nz([qryPartsInv.ItmNett]))) AS [Tot Parts Nett], Sum(Nz([qryMatsInv.ItmNett])) AS [Tot Mats Nett], Sum(Nz([qryOtherInv.ItmNett])) AS [Tot Other Nett], Sum(tblJCV.ProdHoursBooked) AS [Hrs Booked], Sum(Nz([SumOfItmNett])) AS ManCC, tblJCV.repex_code AS REPEX, tblJCV.EstimatorCode, Sum(tblCredits.Credited) AS SumOfCredited
FROM tblCredits RIGHT JOIN (qryManCCar RIGHT JOIN (qryPartsInv RIGHT JOIN (qryMatsInv RIGHT JOIN (qryLabInv RIGHT JOIN (qryOtherInv RIGHT JOIN tblJCV ON qryOtherInv.JobID = tblJCV.JobID) ON qryLabInv.JobID = tblJCV.JobID) ON qryMatsInv.JobID = tblJCV.JobID) ON qryPartsInv.JobID = tblJCV.JobID) ON qryManCCar.JobID = tblJCV.JobID) ON tblCredits.JobID = tblJCV.JobID
GROUP BY tblJCV.WorkProviderName, tblJCV.JobID, tblJCV.WriteOffDate, tblJCV.repex_code, tblJCV.EstimatorCode
HAVING (((tblJCV.WorkProviderName) Is Not Null) AND ((tblJCV.WriteOffDate) Is Null) AND ((tblJCV.EstimatorCode) Not In ("Kevin","Tony")));

[HansV]

It won't work since you don't group by Credited. What exactly do you want to do with Credited if you don't group by it?

[D Willett]

Credited doesn't have to do anything Hans, it's more of an identifier and totally hidden. We know with certain work providers we have to purchase parts and then credit them separately but my report can't distinguish because the sales value of parts is still there. So a separate query 'qryCredits' has been joined so I can identify those cases and set the value of parts sales [Tot Parts] to zero.

[HansV]

I'm sorry, I don't see how this could work.

[D Willett]

I'll have to find a way Hans, I tried this:

Tot Parts: Sum(IIf([Credited]>0,Nz([qryPartsInv.ItmValue],0),Nz([qryPartsInv.ItmValue])))

Made no difference, but it didn't crash out either.

[HansV]

The condition [Credited]>0 doesn't do anything in that expression, you return the sum of qryPartsInv.ItmValue whether the condition is true or not.

[D Willett]

How about this:

Tot Parts: IIf([Credited]>0,[qryPartsInv.ItmValue]-[qryPartsInv.ItmValue],[qryPartsInv.ItmValue])

Seems to be working ??

[HansV]

That is equivalent to

Tot Parts: IIf([Credited]>0,0,[qryPartsInv.ItmValue])

What did you set the Total option for this column to? Sum?

[D Willett]

Yes it is, I didn't see that...
Yes I set it to Sum and doing a load of testing to see the outcome, for now it's returning something at least.

Many Thanks