Solved

Populate field with week number from a date

Posted on 2014-11-26
4
643 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
[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
  • 2
4 Comments
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 100 total points
ID: 40468479
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 51

Accepted Solution

by:
Gustav Brock earned 400 total points
ID: 40468537
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
ID: 40475817
Thank you both. Both solutions would work BUT in my scenario Gustav was spot on.
Cheers Again
John
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40475821
You are welcome!

/gustav
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Viewers will learn how the fundamental information of how to create a table.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

615 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