Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013
    Posts
    70

    Answered: Query - year to date by month

    Greetings, I have a report I run every month that counts all the runs we do year-to-date. It uses a query that has a criteria that I use to tell it what year. I would like to able to run the report on say October 2 and get year-to-date for January - September. Right now it gives me through the current day of October 2. Is there a way to do this without using a Between "[Choose begin date] AND [Choose end date]"? It already knows the year so it starts on January 1. Here is the SQL from the query:

    Code:
    SELECT tblCalls.[Run Year], tblCalls.[Run #], tblCalls.[Run Date], tblCalls.Location, tblCalls.[Call Type], tblCalls.[Other Type], tblCalls.FA, tblCalls.MA, tblCalls.[# Att], tblCalls.ALS, tblCalls.[# of Medics], tblCalls.PCR, tblCallTypeLU.callTypeID, tblCallTypeLU.callType
    FROM tblCallTypeLU INNER JOIN tblCalls ON tblCallTypeLU.callTypeID = tblCalls.[Call Type]
    WHERE (((tblCalls.[Run Year])=[Choose 2 Digit Year]))
    ORDER BY tblCalls.[Run Date];
    I know about the naming problems. I inherited that mess. Thanks, Scott

  2. Best Answer
    Posted by healdem

    "Sorry think i was having a brain fart ignore the previous comment.

    Making some key assumptions that you are never going to run this report outside the current calendar year what you could do is set a between clause to be from the 1st jan currrent year to the current date
    Mybetweenclause = "mydatecolum Between " & cdate (year (date ()) & "/01/01" ) & " and " & date ()

    The cdate funxtion converts a string to a date
    ...which we define as the current year, derived from taking the current system date then suffixed with /01/01. That gives us (at present) 2015/01/01.
    So that expression (currently) decodes to
    Mydatecolumn between 2015/01/01 and 2015/10/5
    ... natch you need to change the mydatecolumn to be the name of the date column in your db.

    If you need to identify data by month then adding month (mydatecolumn) as / to your GROUP BY should do the job

    However this code falls apart if say you run the report in early Jan 2016 but expect data for the previoys year


    Only including whole months is trickier in SQL, but another approach would be to define your date limits in a form and set those as parameters when you open the report. If you need the flexibility to report on previous years data then you are always better off using a form to specify a reports parameter"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider using a GROUP BY clause on the month
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Mar 2013
    Posts
    70
    Hi, thanks for the quick response. I have a group by on the run date set to month in the report. I do not have it in the query. I am not sure how to do it in the query. Can you give a little more guidance? Thanks, Scott.

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry think i was having a brain fart ignore the previous comment.

    Making some key assumptions that you are never going to run this report outside the current calendar year what you could do is set a between clause to be from the 1st jan currrent year to the current date
    Mybetweenclause = "mydatecolum Between " & cdate (year (date ()) & "/01/01" ) & " and " & date ()

    The cdate funxtion converts a string to a date
    ...which we define as the current year, derived from taking the current system date then suffixed with /01/01. That gives us (at present) 2015/01/01.
    So that expression (currently) decodes to
    Mydatecolumn between 2015/01/01 and 2015/10/5
    ... natch you need to change the mydatecolumn to be the name of the date column in your db.

    If you need to identify data by month then adding month (mydatecolumn) as / to your GROUP BY should do the job

    However this code falls apart if say you run the report in early Jan 2016 but expect data for the previoys year


    Only including whole months is trickier in SQL, but another approach would be to define your date limits in a form and set those as parameters when you open the report. If you need the flexibility to report on previous years data then you are always better off using a form to specify a reports parameter
    Last edited by healdem; 10-05-15 at 04:31.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Mar 2013
    Posts
    70
    Thank you very much. I have gone with the form option to get other years as you suggested. I appreciate the help. Best, Scott

Tags for this Thread

Posting Permissions

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