Solved

How do I populate a numeric field with a Week Number

Posted on 2015-02-02
5
135 Views
Last Modified: 2015-02-02
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
0
Comment
Question by:mijwil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40584456
you can use the datepart() function

datepart( "ww",Date())
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40584465
Rather than storing the week number, store the week ending date.  You can always derive the week as you need it.  Add an unbound control to the main form.  Change the Name property to txtWeekEndingDate.  Set its format to short date so

In the BeforeInsert event of the subform  where you are entering the details.

Me.WeekEndingDate = Me.Parent!txtWeekEndingDate

Then when you need the week number, use the DatePart() function to get it.

Select ..., datepart("ww", WeekEndingDate) As WeekNum
From YourTable;
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40584529
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.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40584633
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:
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

Open in new window

/gustav
0
 

Author Closing Comment

by:mijwil
ID: 40584744
Perfect. Thanks
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question