• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 666
  • Last Modified:

after update: get date from week number

Can anyone help me out with this:

I want to calculate the date field on a form with after update the week field. so I fill in week 23 and it has to calculate the date (friday) in june.
0
mvdwal
Asked:
mvdwal
  • 2
  • 2
1 Solution
 
mbizupCommented:
Give this a try in the AfterUpdate event of your weeknumber field:


Me.txtFridayDate =  (Me.txtWeeknum  * 7 + DateSerial(Year(Date), 1, 1) - Weekday(DateSerial(Year(Date), 1, 1))) - 1

Open in new window


- txtWeekNum is the name of the textbox where the week number is entered
- txtFridayDate is the textbox where you want the results displayed
0
 
mvdwalAuthor Commented:
Great! It is working! Thanks a lot. Saves a lot of time. I have the date part working. And I was already guesing the date for a week number.... :-)
0
 
mbizupCommented:
You may also want to provide handling for nulls, 0, negative and invalid entries... here are some ideas, but you can customize it as needed:


      ' if nothing is entered, just exit without doing anything
       if Trim(Me.txtWeeknum & "")  = ""  then exit sub
 
      if isNumeric( Me.txtWeeknum)  = false then
              msgBox "Please enter a valid weeknumber"
               exit sub
      end if

       if Me.txtWeekNumber <= 0 Or Me.txtWeekNumber > 53 then
             msgbox "WeekNumber must be between 1 and 53"
             exit sub
       end if
        

        Me.txtFridayDate =  (Me.txtWeeknum  * 7 + DateSerial(Year(Date), 1, 1) - Weekday(DateSerial(Year(Date), 1, 1))) - 1

Open in new window

0
 
mvdwalAuthor Commented:
Yes you may be right! Thanks a lot!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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