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

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.

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?

--
Duane Hookom
MS Access MVP


"Gina" wrote in message
...
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.

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?



-----Original Message-----
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?

--
Duane Hookom
MS Access MVP


"Gina" wrote in message
...
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.


.

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.
--
Duane Hookom
MS Access MVP


"Gina" wrote in message
...
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?



-----Original Message-----
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?

--
Duane Hookom
MS Access MVP


"Gina" wrote in message
...
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.


.