Solved

Populate field with week number from a date

Posted on 2014-11-26
4
612 Views
Last Modified: 2014-12-02
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
Comment
Question by:mrmad1966
  • 2
4 Comments
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 100 total points
Comment Utility
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 400 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:mrmad1966
Comment Utility
Thank you both. Both solutions would work BUT in my scenario Gustav was spot on.
Cheers Again
John
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You are welcome!

/gustav
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now