• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 745
  • Last Modified:

Week Numbers

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.

0
anya_morris
Asked:
anya_morris
  • 14
  • 12
  • 4
  • +1
2 Solutions
 
peter57rCommented:
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.

 
0
 
anya_morrisAuthor 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.
0
 
peter57rCommented:
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.

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
anya_morrisAuthor 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)

0
 
peter57rCommented:
Here is a sample using start and end dates.

I think usage is obvious, but if not then open the form, enter the values and then click the button.

Look at tblWeeks before and after doing that.

The code is in module1 and the code is claaled from code behind the button.

If you have difficulty following it then post back.  I am out now for a couple of hours but will be baclk later.



db2.mdb
0
 
anya_morrisAuthor 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.
0
 
peter57rCommented:
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") & "#);"
0
 
anya_morrisAuthor 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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Side Note:
Some years technically have 53 weeks :-)

mx
0
 
Gustav BrockCIOCommented:
There is no need to create tables for this.
The maximum week number of a year can be calculated like this:
Public Function ISO_WeekCount( _
  ByVal datYear As Date) _
  As Byte

' Calculates number of weeks in year of datYear according to the ISO 8601:1988 standard.
'
' May be freely used and distributed.
' 2001-06-26. Gustav Brock, Cactus Data ApS, CPH

  Dim bytISO_Thursday As Byte

  ' No special error handling.
  On Error Resume Next
  
  bytISO_Thursday = Weekday(vbThursday, vbMonday)
  
  datYear = DateSerial(Year(datYear), 12, 31)
  ' Subtract one week if datYear is in week no. 1 of next year.
  datYear = DateAdd("ww", Weekday(datYear, vbMonday) < bytISO_Thursday, datYear)
  
  ISO_WeekCount = DatePart("ww", datYear, vbMonday, vbFirstFourDays)
  
End Function

Open in new window


and from the year and the week number you can obtain the first and last date of the week like this:
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


You can even use a callback function (look up the on-line help) for a combobox to display the weeks of the year:
Function ListWeeknumbers( _
  ctl As Control, _
  lngNum As Long, _
  lngRow As Long, _
  lngCol As Long, _
  intCode As Integer) As Variant

' Creates a list of week numbers of current year according to the ISO 8601:1988 standard.

  Select Case intCode
    Case acLBInitialize             ' Initialize.
      ListWeeknumbers = True
    Case acLBOpen                   ' Open.
      ListWeeknumbers = Timer       ' Unique number for control.
    Case acLBGetRowCount            ' Count of rows. Highest week number in current year.
      ListWeeknumbers = ISO_WeekCount(Date)
    Case acLBGetColumnCount         ' Count of columns.
      ListWeeknumbers = 1
    Case acLBGetColumnWidth         ' Width of column.
      ListWeeknumbers = -1          ' Use default width.
    Case acLBGetValue               ' Get data.
      ListWeeknumbers = lngRow + 1  ' Fisrt row is 0.
    Case acLBEnd                    ' Close.
      '                             ' Nothing ...
  End Select

End Function

Open in new window


/gustav
0
 
anya_morrisAuthor Commented:
still trying to work out what to do. will sort this out soon as I need to. thank you.
0
 
Gustav BrockCIOCommented:
The callback function is the fastest and easiest method if you wish to use a combobox or listbox.

/gustav
0
 
anya_morrisAuthor 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.
0
 
Gustav BrockCIOCommented:
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

0
 
anya_morrisAuthor 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?
0
 
Gustav BrockCIOCommented:
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
0
 
anya_morrisAuthor 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.
0
 
Gustav BrockCIOCommented:
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
0
 
Gustav BrockCIOCommented:
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
0
 
anya_morrisAuthor 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.
0
 
Gustav BrockCIOCommented:
ControlSource cannot be a table. A field or empty.

Did your module with the callback function compile?

/gustav
0
 
anya_morrisAuthor 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?
0
 
Gustav BrockCIOCommented:
That is from the menu Debug: Compile and save

It's OK with a separate module.

/gustav
0
 
anya_morrisAuthor Commented:
Debugging produced the following message:
Compile error:
Ambigous name detected: ISO_DateOfWeek
0
 
Gustav BrockCIOCommented:
That's the function above. You need that as well.

/gustav
0
 
anya_morrisAuthor 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.
0
 
Gustav BrockCIOCommented:
You will need to delete the wrong (first) version of ListWeeksOfTwoYears.
Then compile again.

/gustav
0
 
anya_morrisAuthor 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)?
0
 
Gustav BrockCIOCommented:
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
0
 
anya_morrisAuthor Commented:
Very efficient and knowleable. Just wish I could change my original amount of points to much higher.
0
 
Gustav BrockCIOCommented:
You are welcome. Thanks for the kind words!

/gustav
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 14
  • 12
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now