Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Parameter Query Date Serial

    When I run the query I get an error saying that " . . . does not include the specified expression "St No" as part of the aggregate function."

    St No is supposed to be a new field from the below Make Table Query.

    Ninety percent of my parameter queries are by month and year but I have to enter >=01/01/2012 and <=01/31/2012. I just wish to enter a number for the month and a number for the year.

    [Enter month] 1 [Enter Year] 2012

    Code:
    SELECT [TblCARETSData.StreetNumber] AS [St No]
    , TblCARETSData.StreetName AS StName
    , TblCARETSData.City
    , TblCARETSData.ClosingDate AS Dte
    , TblCARETSData.ListPrice AS AskPrice
    , TblCARETSData.ClosePrice AS SalePrice
    , TblCARETSData.ListAgentOfficeKey AS ListID
    , TblCARETSData.ListOfficeName AS ListName
    , TblCARETSData.SaleAgentOfficeKey AS SellID
    , TblCARETSData.SaleOfficeName AS SellName
    , TblCARETSData.ListAgentLastName AS AgentList
    , TblCARETSData.SaleAgentLastName AS AgentSell INTO Scmls
    FROM TblCARETSData
    WHERE TblCARETSData.ClosingDate >=DATESERIAL([Enter Year],[Enter Month],1) And TblCARETSData.ClosingDate <DATEADD("m",1,DATESERIAL([Enter Year],[Enter Month],1)) 
    GROUP BY YEAR(TblCARETSData.ClosingDate), MONTH(TblCARETSData.ClosingDate), county, city, closeprice;
    I'm lost as to what is happening here.

    Thanks . . . Rick

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As an alternative approach consider using a form to validate parameter and then pull those parameters into whatever queries you need. the advantage of this approach is if you need to use the same parameters in multiple forms/reports.
    you can refer to values in a query using forms!myformname!mycontrolname
    you can push values from one form to another using
    forms!mydestinationformname!thetargetcontrol.value = thiscontrol.value
    you can pull values from one form into another using
    mycontrol.value = forms!mysourceformname!mysourcecontrol.value
    natch you need to rename the mywhatevers to reflect the names of the forms and controls in your app

    The design stylistic I tend to use is a form that has the parameters on in locked controls. one first loading it loads with known sane values. the user can calls another form which allows you to implement whatever validation and checking logic you need. once those have been validated thay can be pushed back to the original form.
    the original form allows users to do whatever, but holds the values used in the queries in forms or reports.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2012
    Posts
    5
    Looks like that you have too much criteria on your GROUP BY clause. suggest to limit your group by to a single clause and join it to the rest of the tables.

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    . . ."specified expression [St No] as part of the aggregate function."

    . . . "does not include the specified expression "St No" as part of the aggregate function."

    I've simplified the query a little.

    I can't see what's causing it.

    Thanks . . . Rick

    Code:
    SELECT TblCARETSData.StreetNumber AS [St No]
    , TblCARETSData.StreetName AS StName
    , TblCARETSData.City
    , TblCARETSData.ClosingDate AS Dte
    , TblCARETSData.ListPrice AS AskPrice
    , TblCARETSData.ClosePrice AS SalePrice
    , TblCARETSData.ListAgentOfficeKey AS ListID
    , TblCARETSData.ListOfficeName AS ListName
    , TblCARETSData.SaleAgentOfficeKey AS SellID
    , TblCARETSData.SaleOfficeName AS SellName
    , TblCARETSData.ListAgentLastName AS AgentList
    , TblCARETSData.SaleAgentLastName AS AgentSell
    FROM TblCARETSData
    WHERE [TblCARETSData.ClosingDate]>=[ enter START month ]
    And [TblCARETSData.ClosingDate]<=[enter END month]
    And [TblCARETSData.ClosingDate]=([enter YEAR])
    GROUP BY listname;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •