Solved

Populate field with week number from a date

Posted on 2014-11-26
4
627 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 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 49

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 49

Expert Comment

by:Gustav Brock
ID: 40475821
You are welcome!

/gustav
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

775 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