Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    30

    Unanswered: Need Help with Syntax of "Week Ending Date Calculation"

    Code:
    Public Function FindSaturday(InputDate As Date)
    ' Returns the date of the first Saturday following the Inputdate
       FindSaturday = FormatDateTime(InputDate + (7 - Weekday(InputDate)))
    End Function
    I'd like to use this in one of my fields. Basically it would take another field ( that is populated by the =now() function) and figure out the week ending date.

    Can somone help?

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Code:
    Function FirstSAT(txtDate As Date) As Date
     
    	Dim DayOfWeek, AddDays As Integer
     
    	DayOfWeek = Weekday(txtDate)
     
    	AddDays = 7 - DayOfWeek
     
    	FirstSAT = txtDate + AddDays
     
    End Function
    I put this code in a module.

    Then call the function from VBA (assuming you are populating a textbox called txtFirstSAT).
    Code:
    me.txtFirstSAT = basModule.FirstSAT(DateValue)
    Call it right after the code that is populating the other field.

    Hope this helps!
    Last edited by Cosmos75; 07-16-04 at 16:39.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  3. #3
    Join Date
    Jul 2004
    Posts
    30
    What should (DateValue) equal? Now()?

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Quote Originally Posted by joshua310
    What should (DateValue) equal? Now()?
    You said that there is "another field ( that is populated by the =now() function)". That should be used in the formula of do something like
    Code:
    DateToUse = Now()
    me.txtOtherField = DateToUse
    me.txtFirstSAT = basModule.FirstSAT(DateToUse)
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  5. #5
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Er, I may have misunderstood but why not enter the following directly in the Control Source property?

    =FormatDateTime(Now()+7-Weekday(Now()))

    Unless of course the field is a bound control in which case you must poke the value in programmatically.
    Last edited by Rod; 07-18-04 at 09:44.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •