Determine month and day based on week

I have an Access database that handles ordering of product and has a drop down to specify a delivery week (1 - 52).  I would like a function that can tell me that week 1 is Jan x - x for a given year.  Anyone have something that can do this?  I figured I can add it as a second column to the combobox and make it easier to choose a delivery date.

Thanks,
tech1984Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
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.
0
Jeffrey CoachmanMIS LiasonCommented:
tech1984,

You can't just say Week 1.
Week 1 spans different Dates in each year.
Therefore you need to also specify the year.

Try This:
(the query you want is called: qryWeekRanges

JeffCoachman
Access-EEQ-24561747WeekStarDateW.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
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?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

tech1984Author Commented:
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!!!
0
Jeffrey CoachmanMIS LiasonCommented:
<it looks like I will be updating a bunch of tables every year>
I don't know what you mean by this, please clarify
0
tech1984Author Commented:
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!!!
0
Jeffrey CoachmanMIS LiasonCommented:
<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
0
tech1984Author Commented:
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.
0
Jeffrey CoachmanMIS LiasonCommented:
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?
0
Jeffrey CoachmanMIS LiasonCommented:
Oh, and sorry about the missing table in my sample, I will correct it in the future.
0
tech1984Author Commented:
What I am referring to is week 53 for year 2009 is the same as week 1 for year 2010.
0
Jeffrey CoachmanMIS LiasonCommented:
That is what I was referring to as well.
Did you read my post? (#24861984)
0
tech1984Author Commented:
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!!
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
tech1984Author Commented:
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.
0
peter57rCommented:
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
0
tech1984Author Commented:
Further explanation:
I am using the date range to populate a combobox.  The combobox will span over 2009 across 2010.
0
peter57rCommented:
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.
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
tech1984Author Commented:
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.
0
Jeffrey CoachmanMIS LiasonCommented:
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.
0
tech1984Author Commented:
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.
0
Jeffrey CoachmanMIS LiasonCommented:
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?
0
tech1984Author Commented:
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.
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
tech1984Author Commented:
Thanks for all the help!!  Sorry it took so long to explain.
0
Jeffrey CoachmanMIS LiasonCommented:
Thanks for a spirited debate.
;-)

Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.