# Thread: Query - year to date by month

1. Registered User
Join Date
Mar 2013
Posts
81

## 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

## "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"

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
consider using a GROUP BY clause on the month

4. Registered User
Join Date
Mar 2013
Posts
81
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.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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 03:31.

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