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

[bamenyo]

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.

 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

[zmbd]

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

-z

(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)
:)

[bamenyo]

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

[zmbd]

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.

[zmbd]

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

-Z

[hvsummer]

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

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:

public function day360(DateField as date) as integer
day360 = value(day(DateField - dateserial(Year(datefield), 1, 1)))
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 ==

[NeoPa]

Bamenyo:
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.

[bamenyo]

Thank you all for your time and response.

[zmbd]

bamenyo,
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.