Solved

# after update:  get date from week number

Posted on 2013-06-01
Medium Priority
651 Views
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
Question by:mvdwal
[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
• 2
• 2

LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 39212996
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
``````

- txtWeekNum is the name of the textbox where the week number is entered
- txtFridayDate is the textbox where you want the results displayed
0

Author Comment

ID: 39213000
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

LVL 61

Expert Comment

ID: 39213003
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
``````
0

Author Comment

ID: 39213020
Yes you may be right! Thanks a lot!
0

## Featured Post

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
###### Suggested Courses
Course of the Month11 days, 5 hours left to enroll