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

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"
 
K

Ken Snell [MVP]

GROUP BY clauses will not accept field aliases. You must repeat the entire
expression as one of the fields in the GROUP BY clause.
 
J

James

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Aggregate Function Error 0
the EVIL aggregate function 1
Aggregate Function 3
Aggregate function error 2
Help with aggregate function 6
RE: You tried to execute a query that does not include the specified e 0
Adding fields produces aggregate function error 3
Return of old Access Error "you tried to execute a query that does not include the specified express 4

Top