Current Date, MTD, YTD, Last Year's MTD

[Gina]

Say I have a date (05/29/04). Using this date:

How do I get the MTD date range (which should be 05/01/04 -
05/29/04)?

How do I get the YTD date range (which should be 01/01/04 -
05/29/04)?

How do I get Last Year's MTD date range (which should be
05/01/03 - 05/29/03)?

I need to have all 4 columns (Daily, MTD, YTD and Last Yrs
MTD) on a report. Getting the MTD date range is easy, but
I can't seem to get the YTD and last year's MTD ranges.
Is this possible?

I'd appreciate any help you can provide. Thanks in
advance.

 

[Duane Hookom]

What do you want to get them from? Do you want these as totals in a group by
query? What is your source data and how would you expect to see the results?

 

[Gina]

We have hotel revenue data that is inputted daily (one per
day), and we have an accumulation of years of data. We
would need to start generating a report everyday that will
tell us the Daily revenue and MTD revenue, compared to
last year's MTD revenue. And yes, I want them as totals
in a Group By query. I just want to see the Grand Total
(sum) for the current day, MTD and Last Year's MTD.

Did that answer your question?

 

[Duane Hookom]

Assuming you have field like:
RevDate
RevAmount
To get columns in a query with
TodayRev: Sum(Abs(RevDate = Date()) * RevAmount)
MTDRev: Sum(Abs(Format(RevDate,"yyyymm") = Format(Date(),"yyyymm")) *
RevAmount)
YTDRev: Sum(Abs(Year(RevDate) = Year(Date())) * RevAmount)
To get last year's columns, you can use DateAdd() and/or other functions to
compare date intervals or parts of intervals.