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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: James (jamesb_at_buggeroffspam.opsisltd.co.uk)
Date: 11/04/04 

Date: Thu, 4 Nov 2004 17:45:33 -0000

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"

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)