If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Current Date, MTD, YTD, Last Year's MTD
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. |
#3
|
|||
|
|||
Current Date, MTD, YTD, Last Year's MTD
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. . |
#4
|
|||
|
|||
Current Date, MTD, YTD, Last Year's MTD
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. . |
Thread Tools | |
Display Modes | |
|
|