Solved

Determine month and day based on week

Posted on 2009-07-10
27
1,486 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
ID: 24829315
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
ID: 24829634
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
ID: 24830530
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:tech1984
ID: 24830716
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
ID: 24832046
<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
ID: 24856324
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
ID: 24856600
<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
ID: 24858377
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
ID: 24861984
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
ID: 24862008
Oh, and sorry about the missing table in my sample, I will correct it in the future.
0
 

Author Comment

by:tech1984
ID: 24865376
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
ID: 24866134
That is what I was referring to as well.
Did you read my post? (#24861984)
0
 

Author Comment

by:tech1984
ID: 24875063
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24884295
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
ID: 24895309
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
ID: 24895419
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
ID: 24895696
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
ID: 24896175
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
ID: 24897082
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
ID: 24908361
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
ID: 24909407
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
ID: 24909604
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
ID: 24909744
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
ID: 24909800
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
ID: 24910700
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
ID: 31602335
Thanks for all the help!!  Sorry it took so long to explain.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24914743
Thanks for a spirited debate.
;-)

Jeff
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

860 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