The "You tried to execute... aggregate function... blah" error...

  • Thread starter Thread starter James
  • Start date Start date
J

James

I have a fairly big query I am opening using ADO in visual basic. Back end
DB is Access. The query works okay, until I add an expression field as one
of the returned results, then I get the "You tried to execute a query that
does not use the specified expression <my expression> as an aggregate
function".
I tried putting the expression "as" bit (in the SQL I have my expression AS
SumOfStampDuty) in the Group By but it doesn't help...

the expression is:

FORMAT(iif(UDPchse.StampDutyType = 'Stamp Duty Payable',
iif(UDPchse.PurchasePrice < 60000, '00.00', iif(UDPchse.PurchasePrice <
250001 AND UDPchse.PurchasePrice > 60000, (UDPchse.PurchasePrice / 100)*1,
iif(UDPchse.PurchasePrice < 500001 AND UDPchse.PurchasePrice > 250000,
(UDPchse.PurchasePrice / 100)*3, iif(UDPchse.PurchasePrice > 500000,
(UDPchse.PurchasePrice / 100)*4))))),'#,##0.00') as StampDutyFee

(there is a second one, the iif... blah line in the SQL below)

If I query the expression alone it works, it's only when combined with
everything else it goes wrong.

and the query is: (apart from a load of rubbish...)

"SELECT UDPchse.PortalID, Case.Code, ActHistory.ProcessCode,
ActHistory.Notes, ActHistory.ActionedDate, ActHistory.ActionedTime,
UDPchse.Completiondate, Case.Status, " & _
"UDPchse.PurchasePrice, SUM(UDPchse.ProfessionalFees +
UDPchse.SDLT1Fee) AS NetLegalFee, " & _
"SUM(UDPchse.MortgageSuppFees + UDPchse.MortgageSuppFees2) AS
NetMortgageFee, " & _
"SUM(UDPchse.LocalAuthoritySearch + UDPchse.DrainageEnquiryFee +
UDPchse.EnvironmentSearch + UDPchse.CommonsRegistration +
UDPchse.CoalMiningSearch + UDPchse.CompanySearch +
UDPchse.BankruptcySearchFee + UDPchse.LandRegSearchFee +
UDPchse.TelegraphicTranFee + UDPchse.TTFeeDeposit +
UDPchse.TTFeeSurplusToClient + UDPchse.StampDutyonStockTran +
UDPchse.Courier + UDPchse.MiscCharges " & _
") AS NetDisbursementsFee, " & _
"UDPchse.AmountofBrokerFee, UDPchse.LeaseholdSuppFees, " & _
"SUM(UDPchse.LocalAuthoritySearch + UDPchse.DrainageEnquiryFee +
UDPchse.EnvironmentSearch + UDPchse.CommonsRegistration +
UDPchse.CoalMiningSearch + UDPchse.CompanySearch +
UDPchse.BankruptcySearchFee + UDPchse.LandRegSearchFee +
UDPchse.TelegraphicTranFee + UDPchse.TTFeeDeposit +
UDPchse.TTFeeSurplusToClient + UDPchse.StampDutyonStockTran +
UDPchse.Courier + UDPchse.MiscCharges + UDPchse.AmountofBrokerFee +
UDPchse.LeaseholdSuppFees + UDPchse.ProfessionalFees +
UDPchse.MortgageSuppFees + UDPchse.MortgageSuppFees2 " & _
") AS NetClientFee, " & _
"FORMAT(iif(UDPchse.StampDutyType = 'Stamp Duty Payable',
iif(UDPchse.PurchasePrice < 60000, '00.00', iif(UDPchse.PurchasePrice <
250001 AND UDPchse.PurchasePrice > 60000, (UDPchse.PurchasePrice / 100)*1,
iif(UDPchse.PurchasePrice < 500001 AND UDPchse.PurchasePrice > 250000,
(UDPchse.PurchasePrice / 100)*3, iif(UDPchse.PurchasePrice > 500000,
(UDPchse.PurchasePrice / 100)*4))))),'#,##0.00') as StampDutyFee, " & _
"iif(UDPchse.PurchasePrice < 50001, '40.00',
iif(UDPchse.PurchasePrice < 80001 AND UDPchse.PurchasePrice > 50000,
'60.00', iif(UDPchse.PurchasePrice < 100001 AND UDPchse.PurchasePrice >
80000, '100.00', iif(UDPchse.PurchasePrice < 200001 AND
UDPchse.PurchasePrice > 100000, '150.00', iif(UDPchse.PurchasePrice < 500001
AND UDPchse.PurchasePrice > 200000, '220.00', iif(UDPchse.PurchasePrice <
1000001 AND UDPchse.PurchasePrice > 500000, '420.00',
iif(UDPchse.PurchasePrice > 1000001, '700.00'))))))) " & _
"FROM ActHistory INNER JOIN (Case INNER JOIN UDPchse ON Case.Code =
UDPchse.Code) ON ActHistory.CaseCode = Case.Code " & _
"WHERE (((UDPchse.PortalID) Is Not Null) " & _
"AND (((ActHistory.ProcessCode)='NAT1' Or
(ActHistory.ProcessCode)='LMS01' Or (ActHistory.ProcessCode)='LMS20' Or
(ActHistory.ProcessCode)='NAT3' Or " & _
"(ActHistory.ProcessCode)='CPD08' Or
(ActHistory.ProcessCode)='LMS02' Or (ActHistory.ProcessCode)='PUR06' Or
(ActHistory.ProcessCode)='CP13' Or (ActHistory.ProcessCode)='LMS03' Or
(ActHistory.ProcessCode)='LMS04' Or (ActHistory.ProcessCode)='LMS05' Or " &
_
"(ActHistory.ProcessCode)='CP02' Or (ActHistory.ProcessCode)='CPD16'
Or (ActHistory.ProcessCode)='LMS06' Or (ActHistory.ProcessCode)='LMS24' Or
(ActHistory.ProcessCode)='SAL05' Or (ActHistory.ProcessCode)='CSD20' Or
(ActHistory.ProcessCode)='LMS07' Or " & _
"(ActHistory.ProcessCode)='LMS25' Or (ActHistory.ProcessCode)='NAT4'
Or (ActHistory.ProcessCode)='LMS08' Or (ActHistory.ProcessCode)='NAT5' Or
(ActHistory.ProcessCode)='LMS09' Or (ActHistory.ProcessCode)='LMS26' Or
(ActHistory.ProcessCode)='CPD30' " & _
"Or (ActHistory.ProcessCode)='CSD34' Or
(ActHistory.ProcessCode)='LMS10' Or (ActHistory.ProcessCode)='NAT6' Or
(ActHistory.ProcessCode)='LMS21' Or (ActHistory.ProcessCode)='LMS22' Or
(ActHistory.ProcessCode)='LMS23' Or (ActHistory.ProcessCode)='NAT2'))) " & _
"GROUP BY UDPchse.PortalID, Case.Code, ActHistory.ProcessCode,
ActHistory.Notes, ActHistory.ActionedDate, ActHistory.ActionedTime,
UDPchse.Completiondate, Case.Status, UDPchse.PurchasePrice,
UDPchse.AmountofBrokerFee, UDPchse.LeaseholdSuppFees"
 
GROUP BY clauses will not accept field aliases. You must repeat the entire
expression as one of the fields in the GROUP BY clause.
 
Ken Snell said:
GROUP BY clauses will not accept field aliases. You must repeat the entire
expression as one of the fields in the GROUP BY clause.
Click to expand...
Henk, that sounds messy! I'll give it a bash tomorrow,
Thanks
James
 
Back
Top