We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Week Numbers

anya_morris
anya_morris asked
on
Medium Priority
778 Views
Last Modified: 2012-05-11
Dear Experts,

Please could you advise a newbie about the following. I'm trying to produce a table called tblWeeks with just one field (for now) which displays week numbers for this year 2011. For e.g.

tblWeeks
1
2
...52
So basically I won't even need to manually put in the week numbers in the field, it would populate all the week numbers into a table automatically.

Comment
Watch Question

CERTIFIED EXPERT

Commented:
To do this automatically you have to write code and also have an event that makes the code run.

You say you are new, so assuming you are not comfortable with code as yet, it would be much simpler to create the list of week numbers in an excel spreadsheet (just by draging down). You can create as long a list as you can foresee needing in just a few seconds.  You can then import the list into Access as a table, using the External Data option on the ribbon.

 

Author

Commented:
Thank you peter57r, and yes you are right i'm not comfortable with code yet. I would be willing to experiment though and insert a ready made code where necessary if it is available.

My concern is that the database will not see a week number 1,2,...52 as week numbers and therefore if I request to see which dates the week number corresponds to I won't be able to. I will be making a week number a primary key and linking other information to it. Perhaps I can use a datefield which is called WeekCommencing and then in a form I would be able to use functions to convert it to a week number? Alternatively I'd be very grateful if we can attempt to use some code.
CERTIFIED EXPERT

Commented:
Certainly Access will not see a value of say 43, as meaning week 43, and there is no simple way of converting such a number to a date, although it can be done in a suitable context.

Can you explain how you want to use the table and, as you say, it might turn out to be better to save start and end dates as well as/ rather than week numbers.  In code, it would be as simple to create a table of dates as it would a table of 'week numbers'  and  if that's what you're really after then there's no point in making things more complicated than necessary.

Author

Commented:
The way I want to use the table is: I select a week number (e.g.week 1) and then allocate a person for that week who is responsible for a particular duty, for e.g. Board Keeper. There will be many people allocated for the same week who carry out various duties. This would be in a way a Weekly Call Sheet. I would need to allocate personnel, their duties and their weeks in advance, probably for a whole year. I would also need to see which dates correspond to which weeks.

If I create start and end dates would the table look as follows?
tblWeeks
pk WeekStartDate (date/time field)
pk WeekEndDate (date/time filed)

CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
peter57r, that's brilliant. i have looked at it and there seem to be a problem with the date format. It randomly displays american and english date formats, see below. Could I change the date format to an english one only (dd,mm,yyyy) ?

ID      Startdate      Enddate
157      26/12/2010      01/01/2011
158      01/02/2011      01/08/2011
159      01/09/2011      15/01/2011
160      16/01/2011      22/01/2011

Thank you.
CERTIFIED EXPERT

Commented:
OK.
Not quite what I expected but this will fix it.

Change one line in the code to...

    strsql = strsql & "#" & Format(Startweek, "yyyy-mm-dd") & "#, #" & Format(Endweek, "yyyy-mm-dd") & "#);"

Author

Commented:
this works a treat. thank you.
I will work on transferring the code into my database tomorrow and will probably then close this question. thank you very much again.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Side Note:
Some years technically have 53 weeks :-)

mx
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
still trying to work out what to do. will sort this out soon as I need to. thank you.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
The callback function is the fastest and easiest method if you wish to use a combobox or listbox.

/gustav

Author

Commented:
cactus_data,
thank you for the code.
i've created a combobox, i've put in the Row Source Type ListWeekNumbers in an unbound form. It now displays a list of week numbers from 1 to 52. I've specified that the value is to be stored in a tblWeek field Week. So now when I create a record in a form it automatically inserts a value in my tblWeek. I only see the week number, however I presume the database knows which dates that week represents? I can easily create a query that displays week numbers and their representing dates?
I find this solution a little bit less friendly to use, only because I need to change the weeks as starting from Sunday not Monday and I will need weeks for several years ahead not just for 2011. I don't know how to do this myself and to keep asking you guys to do it for me is rather unfair.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It is a bit difficult as the ISO 8601:1988 standard is the only unambiguous numbering method I know of. That means, using any other method, you probably are a little on your own.

You did ask for the week numbers of 2011 only. The function could be expanded to cover several years if you knew how many.

/gustav

Author

Commented:
gustav,
i had a think and you're right I should follow the iso. i'd like to give it a go. My only problem is how can I see which dates the week numbers represent? Do I need to build a query for that?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
No, for a single lookup you can use this function:
Public Function ISO_DateOfWeek( _
  ByVal intYear As Integer, _
  ByVal bytWeek As Byte, _
  Optional ByVal bytWeekday As Byte = vbMonday) _
  As Date

' Calculates date of requested weekday in a week of
' a year according to ISO 8601:1988 standard.
'
' Notes:  Years less than 100 will be handled as
'         two-digit years of our current year frame.
'         Years less than zero returns a zero date.
'         A weeknumber of zero returns the requested
'         weekday of the week before week 1.
'
' 2000-12-17. Cactus Data ApS, Gustav Brock.

  ' The fourth of January is always included in
  ' the first week of year intYear.
  Const cbytDayOfFirstWeek  As Byte = 4
  ' Number of days in a week.
  Const cbytDaysOfWeek      As Byte = 7
  ' Month of January.
  Const cbytJanuary         As Byte = 1
  
  Dim datDateOfFirstWeek    As Date
  Dim intISOMonday          As Integer
  Dim intISOWeekday         As Integer
  Dim intWeekdayOffset      As Integer
  
  ' No specific error handling.
  On Error Resume Next
    
  If intYear > 0 Then
    ' Weekday of Monday.
    intISOMonday = Weekday(vbMonday, vbMonday)
    ' Date of fourth of January in year intYear.
    datDateOfFirstWeek = DateSerial(intYear, cbytJanuary, cbytDayOfFirstWeek)
    ' Weekday of fourth of January in year intYear.
    intISOWeekday = Weekday(datDateOfFirstWeek, vbMonday)
    ' Calculate offset from Monday in first week of year intYear.
    intWeekdayOffset = intISOMonday - intISOWeekday
    
    ' Weekday of requested weekday.
    intISOWeekday = Weekday(bytWeekday, vbMonday)
    ' Calculate offset from requested weekday in first week of year intYear.
    intWeekdayOffset = intWeekdayOffset + intISOWeekday - intISOMonday
    ' Date of requested weekday in first week of year intYear.
    datDateOfFirstWeek = DateAdd("d", intWeekdayOffset, datDateOfFirstWeek)
  
    ' Date of requested weekday in requested week of year intYear.
    datDateOfFirstWeek = DateAdd("ww", bytWeek - 1, datDateOfFirstWeek)
  End If
  
  ISO_DateOfWeek = datDateOfFirstWeek
  
End Function

Open in new window


like:

datMonday = ISO_DateOfWeek(Year(Date), Me!cboWeekNumber, vbMonday)
datFriday = ISO_DateOfWeek(Year(Date), Me!cboWeekNumber, vbFriday)

However, if you wish the combo- or listbox to display the dates in two additional columns after the weeknumber, the callback function will need a major rewrite; this is also the case if it should return rows for more than a year.
An option, by the way, is to modify the function to show weeknumbers (and dates) for the next year starting at the current week.

/gustav

Author

Commented:
gustav,
Yes, it would be very helpful to
1. have a combo to display the dates in two additional columns and
2. list week numbers for two years, i.e. 2011 and 2012

Thank you.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
OK, here it is:
Function ListWeeksOfTwoYears( _
  ctl As Control, _
  lngId As Long, _
  lngRow As Long, _
  lngCol As Long, _
  intCode As Integer) As Variant

  ' Creates a list of week number, start date, and end date of current and next year
  ' according to the ISO 8601:1988 standard.
  '
  ' 2011-05-02. Gustav Brock, Cactus Data ApS, CPH.

  ' Choose first day of week to display in column(1).
  Const cbytDayFirst    As Byte = vbMonday
  ' Choose last day of week to display in column(2).
  Const cbytDayLast     As Byte = vbFriday
  ' Format of dates.
  Const cstrDateFormat  As String = "yyyy\-mm\-dd"
  
  Static datDateFirst   As Date
  Static datDateLast    As Date
  
  Static intDaysYear1   As Integer
  Static intDaysYear2   As Integer
  
'  Dim intDayDiff        As Integer
  Dim varValue          As Variant
  
  intDayDiff = vbFriday - vbMonday
  Select Case intCode
    Case acLBInitialize           ' Initialize function.
      ' Count of weeks.
      intDaysYear1 = ISO_WeekCount(Year(Date))
      intDaysYear2 = ISO_WeekCount(Year(Date) + 1)
      ' First and last date of first week.
      datDateFirst = ISO_DateOfWeek(Year(Date), 1, cbytDayFirst)
      datDateLast = ISO_DateOfWeek(Year(Date), 1, cbytDayLast)
      varValue = True             ' True to initialize.
    Case acLBOpen
      varValue = Timer            ' Autogenerated unique ID.
    Case acLBGetRowCount          ' Get rows.
      ' Set number of rows.
      varValue = intDaysYear1 + intDaysYear2
    Case acLBGetColumnCount       ' Get columns.
      varValue = 3                ' Set number of columns.
    Case acLBGetColumnWidth       ' Get column width
      ' Set column widths in twips.
      If lngCol = 0 Then
        ' Column width of week number column.
        varValue = 300
      Else
        ' Column width of date columns.
        varValue = 920
      End If
    Case acLBGetValue             ' Get the data.
      If lngCol = 0 Then
        ' Week number of this week.
        varValue = lngRow + 1
        If varValue > intDaysYear1 Then
          ' This is a week number of the second year.
          varValue = varValue - intDaysYear1
        End If
        ' Optional. Right justify week number.
        If varValue < 10 Then
          varValue = Space(2) & varValue
        End If
      Else
        ' Week date.
        If lngCol = 1 Then
          ' Calculate first date of this week.
          varValue = DateAdd("ww", lngRow, datDateFirst)
        Else
          ' Calculate last date of this week.
          varValue = DateAdd("ww", lngRow, datDateLast)
        End If
      End If
    Case acLBGetFormat            ' Format the data.
      If lngCol > 0 Then
        ' Apply date format.
        varValue = cstrDateFormat
      End If
    Case acLBEnd
      ' Do something when form with listbox closes or
      ' listbox is requeried.
  End Select
  
  ' Return Value.
  ListWeeksOfTwoYears = varValue

End Function

Open in new window

It works best for a ListBox.

/gustav
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Oops, small edit:
Function ListWeeksOfTwoYears( _
  ctl As Control, _
  lngId As Long, _
  lngRow As Long, _
  lngCol As Long, _
  intCode As Integer) As Variant

  ' Creates a list of week number, start date, and end date of current and next year
  ' according to the ISO 8601:1988 standard.
  '
  ' 2011-05-02. Gustav Brock, Cactus Data ApS, CPH.

  ' Choose first day of week to display in column(1).
  Const cbytDayFirst    As Byte = vbMonday
  ' Choose last day of week to display in column(2).
  Const cbytDayLast     As Byte = vbFriday
  ' Format of dates.
  Const cstrDateFormat  As String = "yyyy\-mm\-dd"
  
  Static datDateFirst   As Date
  Static datDateLast    As Date
  
  Static intDaysYear1   As Integer
  Static intDaysYear2   As Integer
  
  Dim varValue          As Variant
  
  Select Case intCode
    Case acLBInitialize           ' Initialize function.
      ' Count of weeks.
      intDaysYear1 = ISO_WeekCount(Year(Date))
      intDaysYear2 = ISO_WeekCount(Year(Date) + 1)
      ' First and last date of first week.
      datDateFirst = ISO_DateOfWeek(Year(Date), 1, cbytDayFirst)
      datDateLast = ISO_DateOfWeek(Year(Date), 1, cbytDayLast)
      varValue = True             ' True to initialize.
    Case acLBOpen
      varValue = Timer            ' Autogenerated unique ID.
    Case acLBGetRowCount          ' Get rows.
      ' Set number of rows.
      varValue = intDaysYear1 + intDaysYear2
    Case acLBGetColumnCount       ' Get columns.
      varValue = 3                ' Set number of columns.
    Case acLBGetColumnWidth       ' Get column width
      ' Set column widths in twips.
      If lngCol = 0 Then
        ' Column width of week number column.
        varValue = 300
      Else
        ' Column width of date columns.
        varValue = 920
      End If
    Case acLBGetValue             ' Get the data.
      If lngCol = 0 Then
        ' Week number of this week.
        varValue = lngRow + 1
        If varValue > intDaysYear1 Then
          ' This is a week number of the second year.
          varValue = varValue - intDaysYear1
        End If
        ' Optional. Right justify week number.
        If varValue < 10 Then
          varValue = Space(2) & varValue
        End If
      Else
        ' Week date.
        If lngCol = 1 Then
          ' Calculate first date of this week.
          varValue = DateAdd("ww", lngRow, datDateFirst)
        Else
          ' Calculate last date of this week.
          varValue = DateAdd("ww", lngRow, datDateLast)
        End If
      End If
    Case acLBGetFormat            ' Format the data.
      If lngCol > 0 Then
        ' Apply date format.
        varValue = cstrDateFormat
      End If
    Case acLBEnd
      ' Do something when form with listbox closes or
      ' listbox is requeried.
  End Select
  
  ' Return Value.
  ListWeeksOfTwoYears = varValue

End Function

Open in new window


/gustav

Author

Commented:
I've created a list box, named lstWeekNumber. I then specified in the properties Control Source as table Week and in Row Source Type ListWeeksOfTwoYears. When i try to view the form in Form View I have a warning message that says: "
'ListWeeksOfTwoYears' may not be a valid setting for the RowSourceType property, or there was a compile error in the function.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
ControlSource cannot be a table. A field or empty.

Did your module with the callback function compile?

/gustav

Author

Commented:
Sorry, I don't know what you mean by compiling. What i've done is saved the last code provided into a module 5. The Callback function (i.e.function ListWeeknumbers) module is saved separately in module 3. Do I need to do it differently?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That is from the menu Debug: Compile and save

It's OK with a separate module.

/gustav

Author

Commented:
Debugging produced the following message:
Compile error:
Ambigous name detected: ISO_DateOfWeek
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That's the function above. You need that as well.

/gustav

Author

Commented:
Ok, i've now added the two functions above both called ListWeeksOfTwoYears (i thought that i didn't need the function before your words "oops, small edit"). As for the function ISO_DateOfWeek, I do have it in a separate module, called module 2.

Still the same message appears and the same happens as above when I debug.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You will need to delete the wrong (first) version of ListWeeksOfTwoYears.
Then compile again.

/gustav

Author

Commented:
Blimey, at last. It works perfectly! I will add the max points for that definitely. Thank you very much.

Now I do need to see the dates for the week not only on that form, but in my report and queries. I'd be happy to create a new question for this, but don't want to lose you.

Say I create a query called qryWeek with the following fields:
Field: Week
Table:tblWeek
That will produce a list of the week numbers that are in my table tblWeek but I can't see the dates. What do I do to show the week dates as well (next to the corresponding week number)?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Great!

First, you will need the year as well.
Then use the function above like this:

Queries:
Monday: ISO_DateOfWeek([Year], [Week], 2)
Friday: ISO_DateOfWeek([Year], [Week], 6)

or for current year:
Monday: ISO_DateOfWeek(Year(Date()), [Week], 2)
Friday: ISO_DateOfWeek(Year(Date()), [Week], 6)

Reports:
For Monday of that week:
=ISO_DateOfWeek(intYear, intWeek, 2)

For Tuesday of that week:
=ISO_DateOfWeek(intYear, intWeek, 3)

For Sunday of that week:
=ISO_DateOfWeek(intYear, intWeek, 1)

/gustav

Author

Commented:
Very efficient and knowleable. Just wish I could change my original amount of points to much higher.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome. Thanks for the kind words!

/gustav
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.