434,587 Members | 1,070 Online
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

# dateserial for previous year??

 P: 9 I am needing to calculate a month to date for the previous year in a report. Example: today is 02/18/10 and I have a month to date for today(current year). I need the report to also show month to date for 02/18/09. I believe I would be able to manipulate my current formula that figures the month to date for the current year, but I have been unsuccessful so far. I have the current year, month to date formula of: Expand|Select|Wrap|Line Numbers =DAvg("[Occpncy Rate Skill Level]","[Service Guarentee Daily QRY]","Date Between #" & DateAdd("d",-Day([Date])+1,[Date]) & "# AND #" & [Date] & "#") and I am using the grouping in the report for the month. I am pretty much a novice in comparison to most out here, any help would be appreciated. Thanks Feb 18 '10 #1
7 Replies

 Expert Mod 100+ P: 2,321 Hi there and welcome to Bytes 1) You should avoid using Date as a field name as it is a reserved word, and might end up causing you alot of headache. 2) To subtract a year from field myDate you would do: Expand|Select|Wrap|Line Numbers myOldDate = DateAdd("yyyy", -1, myDate) DateAdd's first parameter specifies that we wish to add years ("yyyy") but you could also use "d" for days, or "m" for months. Second paramter -1 tells the function we wish to ADD -1 years, lastly the myDate is the date we wish to add the -1 year to. 3) Please remember to use the code tags around your code [code][/code]. It makes it a bit easier to read. Feb 18 '10 #2

 P: 9 I may not be putting 2 & 2 together. I did get to show me the date one year ago. But, how do I get it to show me the data from a year ago? I want the report field to show the previous year data (year to date). I'm not sure if I can reference the field I already have that is doing the calculation already and have it display the previous year? Feb 22 '10 #3

 Expert Mod 100+ P: 2,321 I've read your post but im not sure im making sense of what your trying to acomplish. If you want all rows made in the last year, and you have a field called myDate you could select them using a query: Expand|Select|Wrap|Line Numbers SELECT * from myTable where myDate>dateAdd("yyyy",-1,Date()) Feb 22 '10 #4

 P: 9 Sorry for the confusion. I will try to explain further here. I have a query Service Guarentee QRY with a field named Occpncy Rate Skill Level. From this I have a report that shows the daily volume, a month to date. I need to add a third field in the report that will show what the month to date on that day for the previous year was. Each would have a calculated result. Day (1/25/10) MTD(1/1/10-1/25/10) MTD prior year (1/1/09-1/25/09) The formula listed at the start calculates the MTD of the current year. So I am not sure if I can reference that and some how pull the data from the previous year or how to reference that data?? Hope this helps some. Feb 23 '10 #5

 Expert Mod 100+ P: 2,321 What is a "Month to date"? Feb 23 '10 #6

 P: 9 It is a running sum calculated from the start of a month to the current day of the month and would start over each month. Example 01/01/10 - 1/2/10, 1/1/10 - 1/3/10, 1/1/10 - 1/4/10, 1/1/10 - 1/5/10 and so on until the end of the month. I have this already calculated but I need to get the numbers of the MTD for the prior year (which has already been calculated). I need to some how reference that prior year data in my report. Feb 24 '10 #7

 P: 9 I may have misunderstood the question. The month to day is the timeframe of the data I am calculating. I have this calculated already but I need to be able to reference the prior year data for the month to date. So I need to get the data that would show 2/1/09 - 2/24/09 (for current examle of time frame) Feb 24 '10 #8