Determine month and day based on week

You need to say how you want to define week1.

The choices already built in to Access are:

Start with week in which January 1 occurs (default).
Start with the first week that has at least four days in the new year.
Start with first full week of the year.

Not to mention that weeks will often span month boundaries, so what would have to happen in that case,
*and* that neither 365 nor 366 is evenly divisble by 7, so what do you do about the remainder?

peter57r:

I am actually doing this for a client, so I am getting clarification on the how he wants to determine week 1.

boag2000:

Yeah, I knew I would have to specify the year.  I was kinda hoping that there was a function to calculate off the current year or something.  The example you provided with the queries looks great, but it looks like I will be updating a bunch of tables every year... or am I missing something..

Thanks for the quick responses!!!

<it looks like I will be updating a bunch of tables every year>
I don't know what you mean by this, please clarify

boag2000:
WOW!!  Ok, so I didn't look at it closely enough.  I didn't see the form that (which required slight modification) that allowed me to enter my date range to get this going.  This is beautiful!!

One issue: I noticed that I have a 53 week for 2009 that is the same as the 1 week of 2010.  Do you have a quick fix for that?

Thanks a million!!!

<I noticed that I have a 53 week for 2009 that is the same as the 1 week of 2010.  Do you have a quick fix for that?>
I don't understand what you are asking for here.
What is "broken", in my sample that needs a "Fix"?

JeffCoachman

Here is what I did.  I found the form that you created in your sample to add the dates to the table.  I had to modify the form because the table didn't exist (looks like you renamed it).  After I choose a new date range, which was just a smaller subset of the date range you had, I noticed that the last week of 2009 and the first week of 2010 where the same.  

I don't have the exact ranges in front of me, but it was something like week 53 Dec 27 2009 - Jan 2 2010 and week 1 Dec 27 2009 - Jan 2 2010

If this can be fixed that will be great.  If this is how "weeks" are typically assigned than I am cool with it as well.  I am pretty sure there will not be any deliveries on this week any way and that is the whole point of this project (getting a simple explanation of week for delivery).

Thanks again.

Let me know if you need more specific detail and I can get it to you tonight when I am in front of the DB.

Each Week has 7 days, so 12/27/2009 - 1/2/2010 contains Five days from 2009 and two days from 2010.
This should be correct AFAIK.

I this what you are referring to?

Oh, and sorry about the missing table in my sample, I will correct it in the future.

What I am referring to is week 53 for year 2009 is the same as week 1 for year 2010.

That is what I was referring to as well.
Did you read my post? (#24861984)

I don't know if I misread the post or just didn't understand what you were getting at.  :)

So, basically what you are saying is that it is correct to have the last week of a year the same as the first week of the next year if it doesn't equal an exact 7 days?  If so, great!  I simply didn't understand how it worked (don't deal with weeks like this much).

Let me know,
Thanks!!

Again, a week is a 7 day period no matter how you look at it.
(Usually starting with Sunday)

So if December 28 is a Sunday.
That is when the 7 day period (Week) starts.

So day 1 of the week is Sunday Dec 28 (Week 52)
Day 2 of the week is Monday Dec 29 (Week 52)
Day 3 of the week is Tuesday Dec 30 (Week 52)
Day 4 of the week is Wednesday Dec 31 (Week 52)
Day 5 of the week is Thursday January 1 (Week 1, New Year, same week, make sense?)
Day 6 of the week is Friday January 2 (Week 1)
Day 7 of the week is Saturday January 3 (Week 1)

In other words, the 7 day week starts on Sunday, Not Janualy 1st.
A 7 day period (week) can straddle 2 years.
(If a Week started on January 1, and January 1 was a Wedesday, then for that entire year you must change all the calendars so that Wednesday is the first day of the week.)
A 7 day period (week) can straddle 2 years.

Put yet another way, the 7 day period above is both week 52 of 2008 *and* Week of 1 of 2009
It is week 52 of 2008 for 4 days, and Week 1 of 2009 for 3 days.
So there a bit of an issue between how we count "Weeks" as numbers, and how we count weeks as 7 day periods

Want more proof?
Check any calendar that displays the "Week Numbers"
Here is what MS Oulook displays when the "Week Numbers" are turned on:

;-)

JeffCoachman

untitled.JPG

I do understand that a week has to be 7 days.  I do understand that week 53 and week 1 overlap.

What I was getting at is exactly what the calendar shows.  It only shows week 1.  It does not show week 53.  If you put in the date range to overlap the year, the qryWeekRange shows both 53 and 1 as the same range.  Basically I was wondering if it could show up like the calendar and only show week 1.

If this is not possible to code, than it is ok because I don't believe I will ever have deliveries on this week.  I really want to make sure that the question is understood because I think I have possibly explained it poorly in previous posts.

Either way, everything else is great and I really appreciate the help.

Your result will depend on which date you give the function to work with.
 If it's a date in 2008 you will get 2008-53 and if it's a date in 2009 you will get 2009-01

Further explanation:
I am using the date range to populate a combobox.  The combobox will span over 2009 across 2010.

I have to refer you back to the question you were asked (several times) right at the beginning.  You have to say how you are defining week 1.

OK, but I thought the issue was resolved, and that you were just looking for clarification on the definition of a "Week".

<So, basically what you are saying is that it is correct to have the last week of a year the same as the first week of the next year if it doesn't equal an exact 7 days?  If so, great!  I simply didn't understand how it worked (don't deal with weeks like this much).>

JeffCoachman

I guess initially I might have needed a clarification on a week or why the 53 and 1 week had the exact same range.  Now the issue after seeing the screen shot is why can't the query act the same way.  I understand and completely fine with week 1 containing part of the previous year.  I just don't want the duplication of the same period.

Once again, if this is a big issue, I will drop it.  I will accept the solution as is if you feel it is correct.  I really appreciate all the help and am not looking to upset anyone.  I am simply trying to explain and feel I am doing a poor job of doing so.

Again I don't know what you are asking for here?

<why can't the query act the same way.>
Act the same way as what?

Please post a clear example of what you are expecting the output to be.

Week 52 12/20/2009 - 12/26/2009
Week 1  12/27/2009 - 1/2/2010

I don't want week 53 to show.  Currently it is showing with the same range as week 1.

While this can certainly be done, it is not the way it is commonly done.

I, personally have never seen weeks disaplayed in this manner.

Can you provide an example of a system that uses this verbiage?

The calendar screen shot is basically what I am going after.  It shows week 52 and then goes to week 1.  I am doing the same thing, but in a combobox with text.

The Screenshot only displays Week 1 because it does not have the capability of showing two week numbers in the same space.
It simply displays the "Week number" of the last day.

My point is simply this...

If you use Access' built in DatePart() Function and enter this:
  Datepart("WW",#12/27/2009#)  (Meaning you want the WeekNumber for a 12/27/2009)
...you will get 53, ...not 1.
So technically it is incorrect to show 12/27/2009 as week 1 because quite simply, ...it is not.

Put in yet another way, some years will simply have more that 52 weeks.
Some years will have 52 weeks plus 1 day (the 53rd week)
Some years will have 52 weeks plus 2 days
...ect

You insistence on showing only 52 weeks is "Short-hand" for the number of weeks in a year (A "rough" estimate.)
It is akin to saying that 1 year is 365 days (it is "roughly" 365 days. It is actually 365.242199& days)
Or saying that one month is 30 days.
You list of "Weeks" should be based on the "Actual Dates", not shorthand weeks.

So, as my example shows, selecting 12/27/2009 will display Week 53, and 12/27/2009 - 1/2/2010, as the week range.
To be safe, you should display all 3 pieces of data: The Date, The Week Number, and the Week Date Range, to be "Technically" correct and to avoid any chance for ambiguity.

If a package gets delivered on 12/27/2009, it is delivered in week 53, ...not week 1.

JeffCoachman

Thanks for all the help!!  Sorry it took so long to explain.

Thanks for a spirited debate.
;-)

Jeff