Finding WeekNumber

  • Thread starter Thread starter Jazz57
  • Start date Start date
J

Jazz57

Hi

i have a table where i am writing a date, for instances (28-11-2007).

I have to retrieve the week Number of the year for this date, in order i can
make querys by week.

i made a table with week as primary Key and Initial date and Final date

I tried to code with a Dlookup but it's too slow.

Is there any possiiblity to have a control like data time Picker where i can
achieve the week number while i am writing my data on the record ?

Thanks for your help.


i saw that tere is a function (WEEK) for Excel , but is there anything
 
Hi Jazz,

Use this .....

weeknum = DateDiff("ww", DateSerial(Year(your_date), 1, 1), your_date) + 1


Gaurav Kothari
 
I don't wonna temper your enthousiasme but think it's good to ask your
attention to the following:

When the year changes you wil get a porblem with your weekly report because
the last dates of December are set to week 53 and the days in Januari to week
1!!!!
Also porbably you need to connect the year to the week to get the right week
when there is more then a year data in your tables.

I use next functions to get the right values in all circumstances:

=> The variable iFDoW is equal to the FirstDayOfWeekvalue (sunday=1,Monday=2)

' This function creates the right weeknbr,
' due to an error in the default Excel an VB functions!
Function fu_WeekNbr(ByVal dtDate As Date, iFDoW As Integer)
Dim iWknbr_Chk As Integer
fu_WeekNbr = Val(Format(dtDate, "ww", iFDoW, vbFirstFourDays))
If fu_WeekNbr = 53 Then
iWknbr_Chk = Val(Format((dtDate - WeekDay(dtDate, vbMonday) + 5), "ww",
iFDoW, vbFirstFourDays))
End If
If iWknbr_Chk = 1 Then fu_WeekNbr = iWknbr_Chk
End Function
'-------------------------------------------------------------------------
' Get the right WeekYear value belonging to the given date.
Function fu_WeekYear(ByVal dtDate As Date, iFDoW As Integer)
Dim iWknr As Integer
Dim iYear As Integer

iYear = Year(dtDate)
iWknr = fu_WeekNbr(dtDate, iFDoW)
fu_WeekYear = iYear
Select Case Month(dtDate)
Case 1
If iWknr >= 52 Then fu_WeekYear = iYear - 1
Case 12
If iWknr = 1 Then fu_WeekYear = iYear + 1
End Select
End Function
'-------------------------------------------------------------------------
'Concatenate Year and weeknbr
Function fu_YearWkNbr(ByVal dtDate As Date, iFDoWSu1_Mo2 As Integer)
fu_YearWkNbr = fu_WeekYear(dtDate, iFDoWSu1_Mo2) * 100 +
fu_WeekNbr(dtDate, iFDoWSu1_Mo2)
End Function

Based on this I created a tblDate that is filled with al needed date items
(key on Date) like Weeknbr, YearWeekNbr, Quarter, Seasons etc.

Hope it will help you even better.

brgds,
martin
 
Back
Top