Hi,

I'm working on a database were part of it will be based on the weeks of a year. What I would like to know is if it is possible on a form to get this weeknumber in this year as a default input value.

I already found a solution for the year part of my table, but not for the week part.

For the year default input I'm using --> Year(Date()) and this gives me the current year, but if I'm trying Week(Date()) it does not work. Now I wonder if this is possible or not... Furthermore if this is possible, the week should start on a monday instead of a sunday...

Does anyone know if this is possible and if yes how?

Thanks

That's two conditions:

On the On Current event of the form do this:

Dim iWeek, iDay As Integer

iWeek = DatePart("w", Date)
iDay = DatePart("d", Date)

If iDay = 1 Then
   iWeek = iWeek - 1
End If

Me.WeekField = iWeek


Did that work? It recalculates the Week number each time you open the form. You may want to put this code on the Before Insert and Before Update events instead, and put this code on the On Current event in an unbound text box just for display.



mmcdonal

It seems like when I use this code it returns a number that is not the current week... for today's date (29/01/2007) it returns 2 as a week number, but it should be 5 because monday 29/01/07 is the first day of week 5...

On the net I found a code that can be used in excell for calculating the iso-weeknumber. Is there anything I could do with this one? If yes, how should I use it?

here is the code:

Public Function isoweeknum(anydate As Date, optional whichformat As Variant) As Integer

'whichformat: missing or <> 2 then returns week number,
' = 2 then yyww
Dim thisyear As Integer
Dim previousyear As Date
Dim thisyearstart As Date
Dim nextyearstart As Date
Dim yearnum As Integer

thisyear = Year(anydate)
thisyearstart = yearstart(thisyear)
previousyearstart = yearstart(thisyear - 1)
nextyearstart = yearstart(thisyear + 1)
Select Case anydate
    Case Is >= nextyearstart
        isoweeknum = (anydate - nextyearstart) \ 7 + 1
        yearnum = Year(anydate) + 1
    Case Is < thisyearstart
        isoweeknum = (anydate - previousyearstart) \ 7 + 1
        yearnum = Year(anydate) - 1
    Case Else
        isoweeknum = (anydate - thisyearstart) \ 7 + 1
        yearnum = Year(anydate)
End Select

If IsMissing(whichformat) Then
    Exit Function
End If
If whichformat = 2 Then
    isoweeknum = CInt(Format(Right(yearnum, 2), "00") & Format(isoweeknum, "00"))
End If

End Function

Function yearstart(whichyear As Integer) As Date

Dim weekday As Integer
Dim newyear As Date

newyear = DateSerial(whichyear, 1, 1)
weekday = (newyear - 2) Mod 7

If weekday < 4 Then
    yearstart = newyear - weekday
Else
    yearstart = newyear - weekday + 7
End If

End Function

I don't know if this works or not and don't know how to use it. I tried to insert in into a sub, but it doesn't work. I get a nice VBA error message, so I guess it's not possible to insert a function into a sub...

Also, this is based on VBA, what if I have to insert this in a query. If I want that the user can only see data for this week, filtered with a query, than I can't use this function.

I could use a filter on the form itself instead of the query, but if the database will be used on a network, and the database will become bigger, that means that the user will have to wait longer for data to be shown or not?

Sorry, that is DatePart("ww", Date) =)

mmcdonal

Now it works fine :-) But I guess the first day of the week is a sunday. Is it possible to change this to a monday?

There are a couple of ways to do this. I used this method:

iWeek = DatePart("ww", Date)
iDay = DatePart("d", Date)

If iDay = 1 Then
   iWeek = iWeek - 1
End If


However, if you want to be rigorous, you can do this:

Select Case iDay
   Case 1
     iWeek = iWeek - 1
     iDay = 7
   Case 2
     iDay = 1
   Case 3
     iDay = 2
   Case 4
     iDay = 3
   Case 6
     iDay = 5
   Case 7
     iDay = 6
End Select

This renumbers all the days of the week in this variable, to have Monday as te first day of the week. However, if you do this again:

iDay = DatePart("d", Date)

you will go back to the proper day of the week.

Is there a reason you need to do this?

mmcdonal