Solved

after update:  get date from week number

Posted on 2013-06-01
4
631 Views
Last Modified: 2013-06-01
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
Comment
Question by:mvdwal
  • 2
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 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

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
 

Author Comment

by:mvdwal
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

by:mbizup
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

Open in new window

0
 

Author Comment

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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 …
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 …

773 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