Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    21

    Exclamation Unanswered: calendar year to date query

    Hey Guys i need your help
    i need to come up with an SQL statement that selects a range from two variables that are in a table tblRangeDate: startdate, enddate

    the output would be like so: a backwards calendar year based on the selection from these 2 variables
    if i select September the query should bring back everything back from
    9/30/2008 to 10/01/2007

    any ideas i have something like this

    SELECT StartRange(startdate), SUM (CASE StartRange(startdate)... and thats where i get lost ?_?

    thanx for all your help

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try the between operator
    eg
    where startdate between mydatevalue1 and mydatevalue2

    if you need a range of dates ou may need to use a subselect or a having *& group by clause
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT    *
    FROM    tblRangeDate
    WHERE    tblRangeDate.mydate    BETWEEN DATEADD("d", 1, DATEADD("yyyy", -1, #09/30/2008#)) AND #09/30/2008#
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just noticed you select the month initially, not a date. In that case:
    Code:
    Sub datefun()
    
        Dim daMonth As Byte
        Dim daYear As Integer
        Dim startDate As Date
        Dim endDate As Date
        
        daMonth = 9 'September
        daYear = Year(Date)
        
        startDate = DateAdd("d", -1, DateAdd("m", 1, DateSerial(daYear, daMonth, 1)))
        endDate = DateAdd("m", -11, DateSerial(daYear, daMonth, 1))
        
        Debug.Print startDate
        Debug.Print endDate
    
    End Sub
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2006
    Posts
    21
    thanx pootle flump for the code snippet but i'm trying to do it dynamically i need to stick what i have already in the table i have it like so

    startdate enddate
    12/20/2008 01/01/2008

    this will always change as this table is a dump table and will be deleted so we i hit my runReport button it will always have the selection that the user tried to input in the main menu.

    so i went ahead and did this... but it doesn't seem to like it...


    Code:
    strSql = "SELECT * FROM tblRangeDate WHERE tblRangeDate = startDate BETWEEN DATEADD("d", 1, DATEADD(& startDate &, -1)) AND & endDate"

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry but that code doesn't make any sense, nor can I tell what it is trying to do. Please can you just explain what rows you want and what the criteria are? (forget about the dump table thing - just explain in English)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2006
    Posts
    21
    i think i got it...

    SELECT entries_prod.locationId, Format([entryDate],"yyyy-mm") AS Expr1, Sum(entries_prod.eTestHrs) AS SumOfeTestHrs INTO tblTempData
    FROM entries_prod INNER JOIN tblTests ON entries_prod.locationId = tblTests.id
    WHERE (((entries_prod.entryDate)>=[forms].[frmUtilSpecTestMenu].[form].[txtStartRange] And (entries_prod.entryDate)<=[forms].[frmUtilSpecTestMenu].[form].[txtEndRange]))
    GROUP BY entries_prod.locationId, Format([entryDate],"yyyy-mm")
    ORDER BY entries_prod.locationId, Format([entryDate],"yyyy-mm");


    thats what i think i need...

Posting Permissions

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