Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Populate field with week number from a date

Posted on 2014-11-26
4
Medium Priority
?
655 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 400 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 1600 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

670 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