Same week last year, MTD

[Guest]

Using Access 2003

I have searched for quite a while through the discussions but can't seem to
find answers to what I need. If I've missed it, feel free to direct me to a
prior thread.

I need to create a query for a sales analysis report showing columns for:
This week
Same week last year
MTD (month to date) this year
Same period last year

The user enters the start and end dates for the desired week this year. (our
week runs from Monday to Sunday). The query for selecting the sales records
for this week this year is easy, selecting the data dates between the start
and end dates.

First question: How do I determine the start and end dates for the same
week last year?
Second question: How do I determine the start and end dates for the current
month to date and the same for same period last year?

Thanks!
Steve

 

[Guest]

Hi Steve,

Working out the dates for weeks are quite difficult as weeks straddle months
and years.

What I would suggest that if you use weeks a lot then you create a table for
week number and have the start date in there and the end date (although not
really needed) and then in your sales tranactions you add a week number field.

So with this method you work out the week number and use the year -1 for
last year week number.


The other way to do it is to use the datediff function but you need to look
at your week number rules to clarify how you use datediff.

Anotherway to get week number is to use the text(date,"xx") but I can't
recall the exact code (no help on this one) so check the date formats and one
of them.

The month is if dd is a date variable:
and dd =today() '(or now())
start_month = dateserial(year(dd), month(dd),1)
end_month=dateserial(year(dd), month(dd)+1,1)-1
ly_start_month = dateserial(year(start_month)-1, month(start_month),1)
ly_end_month=dateserial(year(end_month)-1, month(end_month)+1,1)-1

 

[Guest]

Martin:

Thank you for your reply. I was hoping to use code and avoid having to
maintain a calendar table with date ranges for multiple years.

I currently format the inputted dates into the week number. My dilemma in
using code instead of a calendar table is to determine the beginning and
ending dates for the same week number for last year.

 

[Joshua A. Booker]

Steve,

You can use the Week(Date) function to return the week of a given date.
Rather than trying to determine the start and end dates for a week across
multiple years can't use use the week number as your criteria? The week
function can handle the diff dates for weeks across multiple years.

Some years I've seen it return 53 weeks though...I think it depends on where
the weekdays fall in the first last weeks of the year.

HTH,
Josh

 

[Guest]

Here is an expression that will return the starting date of the "same week
last year" based on the current date.
dateadd("ww", -52,DateAdd("d", vbMonday - DatePart("w", Date), Date))

For example, For today's date (2/7/2007), it will return 2/6/2006 because
this week's monday is 2/5/2007

This part:
DateAdd("d", vbMonday - DatePart("w", Date), Date)

Returns the Monday for the current week.

If you want to know the week ending date:
dateadd("d",6,WeekStartDate)
That is, the result or the expression above that returns the week start
date. or:
dateadd("d",6,DateAdd("d", vbMonday - DatePart("w", Date), Date))

For the month to date last year:
dateserial(Year(date)-1,Month(date),1) will return the first day of the
current month of last year.

 

[Guest]

The weekly formula gives me just what I'm looking for. Thank you.

The month beginning formula, however, just gives me the first of the month.
Our weeks begin on Monday, so, for example, the first day of February is
1/29/07. February begins with the first day of the 5th week. Does this make
sense?

Perhaps there's a function that recognizes the 4-4-5 pattern (weeks/month)
and can determine the first week number of a month? Or maybe I need to
figure this up in a formula??

 

[Guest]

This is one of the reasons I hate Accountants!

I am not exactly sure this will give you what you are needing, but my first
thought is to use the expression to calculate the first (calendar) day of the
month, then run that date through the expression that gives you the beginning
day (Monday) of the week. For example, if you calculate the first day of
February to be 2/1/2007 and look for that date's Monday, it will be
1/29/2007. For March (3/1/2007) it would return 2/26/2007. Hopefully that
will do it. If not, post back and let's see what we can do.

 

[Guest]

Thanks Dave. This is very helpful. I'll try this.

Since you seem to be an expert on dates, can you give me a formula to show
the beginning date and end date given only the week number?

 

[Guest]

How can I be an expert on dates when I seldom get one?
Can you give me an example of what you want?

 

[Guest]

HA!

A user will key into a week field the number 5 (for example)
I need to calculate that the beginning of that week (if Monday is my week
start) is 1/29/07 and the end of that week is 2/4/07.

 

[Guest]

sorry, Steve, I am still a little confused. I don't know what you are
looking for. If I add 5 weeks to 1/29/2007, I get 3/5/2007.
Can you explain what the objective is? Is it that you are trying to
detemine the beginning date of the next period based on the begining date of
the current period?

 

[Guest]

My user is telling me that he wants to see a sales analysis of Week 5. I
then need to translate that into dates for report headings and for selecting
data (which is stamped with the date, not the week number, and I can't change
that). So, if he tells me week 5, I need to know that week 5 translates to
1/29/07 - 2/4/07.

 

[Guest]

Week 5 from what date? the dates you are showing in the example are the
beginning and ending weeks of week 1 of 2007. Week 5 of 2007 would be
3/5/2007
and from 2/4/07 would be 3/11/2007.

Pardon my thickness.

 

[Guest]

Oh ... gotcha. Week 5 of 2007, which is 1/29/07 - 2/4/07

 

[Guest]

Oh, okay, I got it. So, if 2007 stated on 1/1/2007, then to get the 5th
week, you add 4 to it using the dateadd function to get 1/29/2007

Now, if the user wants to know the beginning day of the 5th week of the year
by putting a 5 in the text box. It would be:

dtmFirstWeek = DateSerial(Year(Date),1,1)
dtmFirstWeek = DateAdd("d", vbMonday - DatePart("w", dtmFirstWeek), _
dtmFirstWeek)
dtmSelectWeekStart= DateAdd("ww",Me.txtWeekNumber -1,dtmFirstWeek)
dtmSelectWeekEnd = DateAdd("d", vbFriday - DatePart("w", _
dtmSelectWeekStart), dtmSelectWeekStart)