Solved

Determine month and day based on week

Posted on 2009-07-10
27
1,372 Views
Last Modified: 2013-11-27
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,
0
Comment
Question by:tech1984
  • 12
  • 11
  • 3
  • +1
27 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
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
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
 

Author Comment

by:tech1984
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<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
 

Author Comment

by:tech1984
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<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
 

Author Comment

by:tech1984
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Oh, and sorry about the missing table in my sample, I will correct it in the future.
0
 

Author Comment

by:tech1984
Comment Utility
What I am referring to is week 53 for year 2009 is the same as week 1 for year 2010.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
That is what I was referring to as well.
Did you read my post? (#24861984)
0
 

Author Comment

by:tech1984
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:tech1984
Comment Utility
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
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
 

Author Comment

by:tech1984
Comment Utility
Further explanation:
I am using the date range to populate a combobox.  The combobox will span over 2009 across 2010.
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:tech1984
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:tech1984
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:tech1984
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Closing Comment

by:tech1984
Comment Utility
Thanks for all the help!!  Sorry it took so long to explain.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Thanks for a spirited debate.
;-)

Jeff
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now