dateserial for previous year??

[beaudreaux]

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:

=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

[TheSmileyCoder]

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:

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.

[beaudreaux]

previous data

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?

[TheSmileyCoder]

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:

SELECT * from myTable where myDate>dateAdd("yyyy",-1,Date())

[beaudreaux]

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.

[TheSmileyCoder]

What is a "Month to date"?

[beaudreaux]

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.

[beaudreaux]

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)