472,961 Members | 1,792 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,961 software developers and data experts.

Populate Week Start and Week End dates given the Year and Week Number in MS Access

I have a combo box on access form from which a user can select a particular year, say 2014 from a drop-down. Another combo box exists on this form which has a drop-down of the week numbers from 1 to 53. These two combo boxes are not linked. (Or do I have to link/bound them?)

Also, there are two text boxes named, "WeekStart" and "WeekEnd" on this form.

What I want to do is to have the week start and end dates for the selected week number and year to be populated in the "WeekStart" and "WeekEnd" textboxes. I'm starting my week on a Sunday. I put this code in the "After Update" event of the "week number" combo box.

Expand|Select|Wrap|Line Numbers
  1.  Me.WeekStart = DateSerial(Me.cboYear, 1, Me.cboWeekNum * 7)
But I know this is not right, and I'm stucked. Any help would be appreciated. And also, is it essential that I bound the week number to the year? If so, how do I go about that too. Thank you
Dec 23 '15 #1
8 3675
5,501 Expert Mod 4TB
How are you defining the week?
Week 0 and week 53 don't make much sense without that information.

For example (in the USA anyway :-) ), in Outlook, 2016-01-01 which lands on Friday of next week shows as week number one; therefor, technically 2016 week one has a start date of 2015-12-27 and end of 2016-01-02. Respectively, week 52 in year 2015 has a start date of 2015-12-20 and an end day of 2015-12-26


(on a related note: BUG: Format or DatePart Functions Can Return Wrong Week Number for Last Monday in Year has an example code to implement a WeekNum() function in Access that follows ISO 8601 rules)
Dec 23 '15 #2
I'm sorry about the error on week 0. It should be week 1. However, some years have 52 weeks, and others have 53 weeks. Example: 2014 has 52 weeks, 2015 has 53 weeks, 2016 has 52 weeks.
Please Check from this website: http://www.epochconverter.com/date-a....php?year=2014
Dec 23 '15 #3
5,501 Expert Mod 4TB
Yes, because the 31st is on the Thursday.
Outlook has it wrong... chuckle

Thought I had seen this done before... see if this article helps: How to Find the First and Last Day in a Given Week, Month, or Year in Access 2010

Just need to determine the Sunday for the given week... then one could plug that into the above article's code

seems a bit kludged.
Dec 23 '15 #4
5,501 Expert Mod 4TB
My thoughts:
(+) For the following I will be working with the first day of the week as Sunday - I'm in the US; thus, this is where I'll start, should be an easy modification to use Monday etc...

(+) Using either a table with 1-53 (or my Cartesian-product-exapansion-query) limited to either 52 or 53 for the year selected.
I would use the weeknumber() function given in the ms article and feed it 12/31 for the year from the year combobox and update the week number combobox as in cascade.

Doing this will make your coding much simpler as one will not have to check for 53 in a 52 year.

(a) Using first day of the week concept, find the Sunday for the week containing the first of the year for the selected year(for example 2015-12-27 for this year)

(b) feed 1/1/yyyy to WeekNumber() and if this is week one then we have the first Sunday of the year, if not and we're in week 53, then dateadd("ww",1,{SundayStepA}) - this then has to be the first week, thus, the first Sunday of week one.
(for example, using step(a) we get 2015-12-27 for the week containing 2016-01-01, feeding 2016-01-01 in to the WeekNumber() yields 53 so dateadd("ww",1,#12/27/2015#) results in 2016-01-03 which is the Sunday in week one for 2016

(c) dateadd("ww",(cboWeekNum-1),SundayFromStepB) to determine the first Sunday in the week number selected

(d) dateadd("d",6,SundayFromStepC) to determine the Saturday

Anyway, the logic worked in my test database when compared against the website you referenced earlier... of course, it threw me initially as they are using Monday as the first day of the week. :)

There is most likely a simpler method - maybe even one in SQL. I am cautious about using the datepart() for the Bug as given in Post#2

Dec 23 '15 #5
215 128KB
when I read this question, I just remember about the day360 in excel which can return exactly day in 360.

it can replace the day parameter (Me.cboWeekNum * 7) in dateserial that you used
Expand|Select|Wrap|Line Numbers
  1. DateSerial(Me.cboYear, 1, Me.cboWeekNum * 7)
weeknumber can be wrong, but if you use exactly day, you can get more accuracy day.
day360 can be rewrite into VBA access like this:
Expand|Select|Wrap|Line Numbers
  1. public function day360(DateField as date) as integer
  2. day360 = value(day(DateField - dateserial(Year(datefield), 1, 1)))
  3. end function
I'm confusing on this part:
1/ if you use dateserial() it'll return date not week
then what you really want to put on the Me.Weekstart ?
2/ Dateserial(year, month, day)
but in your expression
Dateserial(year, 1, week*7)
if week >= 5, then what will it return ?
dateserial of January that year which day = 10 * 7 = 70 ?
day 70 of january really confusing me ==
Dec 25 '15 #6
32,547 Expert Mod 16PB
Please Check from this website: http://www.epochconverter.com/date-a....php?year=2014
A better idea would be for you to complete the question properly in here. If you post a question you're required (Not too surprisingly TBF.) to post the whole question clearly. Expecting experts to research even what your question should have been is not an acceptable way to post on this site.
Dec 27 '15 #7
Thank you all for your time and response.
Dec 29 '15 #8
5,501 Expert Mod 4TB
Were you able to get something to work from the posting here?
If not then perhaps you can provide more details as to what you are actually after.

If you need true ISO8601 where the week starts with Monday and ends on Sunday then Post#5-Step(a) is simply modified to find the first Monday of the week containing the 1st of the year selected. The remaining steps will still be valid.

If you are having issues with your script, then please post it (properly formatted using the [CODE/] tool please) and we'll happily take a look at it.
Dec 29 '15 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

by: bad | last post by:
ive two variables the year and the week (2003 and 5) that means the 5th week of the year 2003. now i need the start- and enddate of the 5th week of year 2003. i hope someone can help me to...
by: Fendi Baba | last post by:
The function is called from opencalendar(targetfield). Thanks for any hints on what could be the problem. .............................................................. var...
by: Bullschmidt | last post by:
If I know the week number and the year, how can I calc the beginning and ending dates of the week? For background I'm going to do some grouping by week but don't just want to call the weeks Week...
by: Shuffs | last post by:
Could someone, anyone please tell me what I need to amend, to get this function to take Sunday as the first day of the week? I amended the Weekday parts to vbSunday (in my code, not the code...
by: Rustan | last post by:
Hi Im using GregorianCalendar to find out the current years week numbers. When the user chooses a week number in a dropdown i want to show that week in a table with the corresponding dates. For...
by: Tim Chase | last post by:
I've been trying to come up with a good algorithm for determining the starting and ending dates given the week number (as defined by the strftime("%W") function). My preference would be for a...
by: Charlotte | last post by:
Hi, is it possible to determine the first and the last day of a week (with classic ASP) nu = "06/01/2008" week = (DatePart("ww",nu)) firstday = dateadd("d",(-datepart("w",date())),nu)...
by: Vince | last post by:
Given a week Number, how do I calculate the date that for the Monday of that week?
by: =?Utf-8?B?cm9kY2hhcg==?= | last post by:
hey all, is there a way if you are given a Year and a Week an easy way to go back say 26 weeks ago from given year/week. for example, given: 2008/16 26 weeks prior is: 2007/43 thanks,
by: jaeprov7 | last post by:
I am trying to create a form with a drop down box that lets you choose the "Week of" and when chosen it will automatically populate the dates for the week on the form so that users can populate...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.