Results 1 to 7 of 7

Thread: Query

  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Query

    Hi: When i run my query between two dates its not sum WM_Amt on the basis of two dates

    SELECT tbl_WebMail.CRC, Sum(tbl_WebMail.WM_Amt) AS Totamnt, tbl_WebMail.WM_Date
    FROM tbl_WebMail
    GROUP BY tbl_WebMail.CRC, tbl_WebMail.WM_Date
    HAVING (((tbl_WebMail.WM_Date) Between [Forms]![frmWeb_Mailfilter]![txtdate1] And [Forms]![frmWeb_Mailfilter]![txtdate2]));

    CRC Totamnt
    Acito, Lisa 20
    Acito, Lisa 40
    Acito, Lisa 60
    Barnes, Sylvia 7
    Barnes, Sylvia 3

    Must Be
    Acito, Lisa 120
    Barnes, Sylvia 10

    Thanks,

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    try:

    SELECT tbl_WebMail.CRC, Sum(tbl_WebMail.WM_Amt) AS Totamnt
    FROM tbl_WebMail
    GROUP BY tbl_WebMail.CRC
    HAVING (((tbl_WebMail.WM_Date) Between [Forms]![frmWeb_Mailfilter]![txtdate1] And [Forms]![frmWeb_Mailfilter]![txtdate2]));

    hth
    Chris

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    what happened to WM_Date in your example output???

    you provided inaccurate data so the best i can offer is a guess: remove WM_Date from the SELECT and GROUP BY clauses

    izy


    forgot to hit the GO button - at least we both had the same idea.
    Last edited by izyrider; 09-10-06 at 12:46. Reason: inadvertant theft of howey's reply
    currently using SS 2008R2

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by izyrider
    at least we both had the same idea.
    Hopefully it'll appear more convincing

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    even more convincing if you use a WHERE clause instead of HAVING, because if WM_Date is removed from both the SELECT and the GROUP BY, it's invalid in the HAVING

    besides, it's way more efficient in the WHERE clause

    SELECT tbl_WebMail.CRC, Sum(tbl_WebMail.WM_Amt) AS Totamnt
    FROM tbl_WebMail
    WHERE tbl_WebMail.WM_Date Between [Forms]![frmWeb_Mailfilter]![txtdate1] And [Forms]![frmWeb_Mailfilter]![txtdate2]
    GROUP BY tbl_WebMail.CRC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2006
    Posts
    157

    Prompting for date

    Prompting for date

    I dont know why its prompting for date as i already put date in my form

    SELECT tbl_WebMail.CRC, Sum(tbl_WebMail.WM_Amt) AS Totamnt
    FROM tbl_WebMail
    GROUP BY tbl_WebMail.CRC;


    QUERY IN FORM
    -------------------
    DoCmd.OpenReport "rpt_qrywebmail2", acViewPreview, wherecondition:="([WM_Date] between #" & Forms.frmWeb_Mailfilter.txtDate1 & "# and #" & Forms.frmWeb_Mailfilter.txtDate2 & "# and [crc] = '" & Me.ddlcrc & "')"

  7. #7
    Join Date
    Jul 2006
    Posts
    157

    Date

    Is this is necessary to use date in my report too. As right now when i enter date from the from it again prompt for the date. And i dont know from where its prompt. WM_Date is not define in my report. But i dont know where it prompt me.

    Thanks,

Posting Permissions

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