mijwil
asked on
How do I populate a numeric field with a Week Number
I have a labor record for employees where we record the hours worked associated with a week number. The week number is formatted as a numeric field.
What I am looking to do is have an unbound control where the date is entered, (like week ending date), have it return "ww" week number format and it becomes the default input for the records that follow.
I am a novice and do not know how to turn a date format result into a number format in another field.
unbound field in header = [weekEnding] ; basically date() ww
bound field = [tblEmployeeHours_WeekNo] ; format number
mij
What I am looking to do is have an unbound control where the date is entered, (like week ending date), have it return "ww" week number format and it becomes the default input for the records that follow.
I am a novice and do not know how to turn a date format result into a number format in another field.
unbound field in header = [weekEnding] ; basically date() ww
bound field = [tblEmployeeHours_WeekNo] ; format number
mij
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
no points please. I tend to agree with Pat on this one. I prefer to store a date, from which I can generate year, month, day, weekday, week, ...
If you store the week #, you will also need to include a field in your table for year.
If you store the week #, you will also need to include a field in your table for year.
Store the date and later retrieve the year-week. This is, however, not as easy as you may think as Access always has been buggy calculating true week numbers.
This function does it right:
This function does it right:
Public Function ISO_WeekYearNumber( _
ByVal datDate As Date, _
Optional ByRef intYear As Integer, _
Optional ByRef bytWeek As Byte) _
As String
' Calculates and returns year and week number for date datDate according to the ISO 8601:1988 standard.
' Optionally returns numeric year and week.
' 1998-2007, Gustav Brock, Cactus Data ApS, CPH.
' May be freely used and distributed.
Const cbytFirstWeekOfAnyYear As Byte = 1
Const cbytLastWeekOfLeapYear As Byte = 53
Const cbytMonthJanuary As Byte = 1
Const cbytMonthDecember As Byte = 12
Const cstrSeparatorYearWeek As String = "W"
Dim bytMonth As Byte
Dim bytISOThursday As Byte
Dim datLastDayOfYear As Date
intYear = Year(datDate)
bytMonth = Month(datDate)
bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)
If bytWeek = cbytLastWeekOfLeapYear Then
bytISOThursday = Weekday(vbThursday, vbMonday)
datLastDayOfYear = DateSerial(intYear, cbytMonthDecember, 31)
If Weekday(datLastDayOfYear, vbMonday) >= bytISOThursday Then
' OK, week count of 53 is caused by leap year.
Else
' Correct for Access97/2000+ bug.
bytWeek = cbytFirstWeekOfAnyYear
End If
End If
' Adjust year where week number belongs to next or previous year.
If bytMonth = cbytMonthJanuary Then
If bytWeek >= cbytLastWeekOfLeapYear - 1 Then
' This is an early date of January belonging to the last week of the previous year.
intYear = intYear - 1
End If
ElseIf bytMonth = cbytMonthDecember Then
If bytWeek = cbytFirstWeekOfAnyYear Then
' This is a late date of December belonging to the first week of the next year.
intYear = intYear + 1
End If
End If
ISO_WeekYearNumber = CStr(intYear) & cstrSeparatorYearWeek & Format(bytWeek, "00")
End Function
/gustav
ASKER
Perfect. Thanks
In the BeforeInsert event of the subform where you are entering the details.
Me.WeekEndingDate = Me.Parent!txtWeekEndingDat
Then when you need the week number, use the DatePart() function to get it.
Select ..., datepart("ww", WeekEndingDate) As WeekNum
From YourTable;