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