Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

Populate field with week number from a date

Folks, I have a form that a user fills in and the data creates a new record in the master table. Two of the current fields are TrainingDate and week number. (ATM the user manually works out the week number and inputs it into the week number field). I need to state here that I am a newbie and as such a real novice with Access but Im sure there must be a way to totally remove the week number from the form and have it auto populate in the master table using the date that is input into the TrainingDate. I am aware of the DatePart function but unsure how to implement or eve n if that would be the recommended way to achieve this. Help appreciated..
0
mrmad1966
Asked:
mrmad1966
  • 2
2 Solutions
 
MacroShadowCommented:
Format(Date, "ww") will return the week number for the given date as text and DatePart("ww", Date) will return the week number as a number.  The easiest way to implement this is to keep the week text box and set its visible property to false, then in the TrainingDate after update event add the following (if the underlying week number field is of text type):
WeekNumber = Format(TrainingDate, "ww")

Open in new window

or if the week number is of number type:
WeekNumber = DatePart("ww", TrainingDate)

Open in new window


reference: http://www.snb-vba.eu/VBA_ISO_weeknummer_en.html
0
 
Gustav BrockCIOCommented:
You probably wish the ISO weeknumber but Access can't provide that natively. You'll have to use a function like that below (copy and paste into a new module, compile and save).

Then use this expression as controlsource for the weeknumber displayed on the form:

=ISO_WeekNumber([YourDateFieldName])

Note that you don't have to store the weeknumber in the table as it can be calculated any time you need it.
Public Function ISO_WeekNumber( _
  ByVal datDate As Date) _
  As Byte

' Calculates and returns week number for date datDate according to the ISO 8601:1988 standard.
' 1998-2000, Gustav Brock, Cactus Data ApS, CPH.
' May be freely used and distributed.

  Const cbytFirstWeekOfAnyYear  As Byte = 1
  Const cbytLastWeekOfLeapYear  As Byte = 53
  
  Dim bytWeek                   As Byte
  Dim bytISOThursday            As Byte
  Dim datLastDayOfYear          As Date

  bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)
  
  If bytWeek = cbytLastWeekOfLeapYear Then
    bytISOThursday = Weekday(vbThursday, vbMonday)
    datLastDayOfYear = DateSerial(Year(datDate), 12, 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
  
  ISO_WeekNumber = bytWeek

End Function

Open in new window

/gustav
0
 
mrmad1966Author Commented:
Thank you both. Both solutions would work BUT in my scenario Gustav was spot on.
Cheers Again
John
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now