Results 1 to 5 of 5

Thread: Date functions

  1. #1
    Join Date
    Jan 2004
    Posts
    106

    Unanswered: Date functions

    i need to come up with a conditional macro that returns a date one month from the original date, but if the return date is a saturday or a sunday, it moves to monday
    please help
    thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Public Function BackAMonth(From As Date) As Date
    BackAMonth = DateAdd("M", -1, From)
    Select Case Weekday(BackAMonth, vbMonday)
    Case 6
    BackAMonth = BackAMonth + 2
    Case 7
    BackAMonth = BackAMonth + 1
    End Select
    End Function

  3. #3
    Join Date
    Jan 2004
    Posts
    106
    Originally posted by namliam
    Public Function BackAMonth(From As Date) As Date
    BackAMonth = DateAdd("M", -1, From)
    Select Case Weekday(BackAMonth, vbMonday)
    Case 6
    BackAMonth = BackAMonth + 2
    Case 7
    BackAMonth = BackAMonth + 1
    End Select
    End Function
    i need to add on one calendar month not subtract. the original date is "date borrowed" and the calculated date in "date Due Back" in the "loan" table
    could you please modify that original solution so i can understand it more?
    where do i type all this?
    thanks!

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Please excuse the previous error and uncommented code. I hope this is what you want and is clear enough to you.
    Code:
    Public Function ForwardAMonth(From As Date) As Date
        ForwardAMonth = DateAdd("M", 1, From) 'adds a month
        Select Case Weekday(ForwardAMonth, vbMonday) 'get the weekday of the new date
            Case 6 'if saturday (weekday 6 from monday)
                ForwardAMonth = ForwardMonth + 2 'add 2 days
            Case 7 'if sunday (weekday 7 from monday)
                ForwardAMonth = ForwardAMonth + 1 'add 1 day
        End Select
    End Function
    Create a module in your db, paste the code.

    You can now call it from anywhere using
    ForwardAMonth([date borrowed])

    Tip: Dont use spaces in any names of anything in a DB!

    Regards

  5. #5
    Join Date
    Jan 2004
    Posts
    106
    Originally posted by namliam
    Please excuse the previous error and uncommented code. I hope this is what you want and is clear enough to you.
    Code:
    Public Function ForwardAMonth(From As Date) As Date
        ForwardAMonth = DateAdd("M", 1, From) 'adds a month
        Select Case Weekday(ForwardAMonth, vbMonday) 'get the weekday of the new date
            Case 6 'if saturday (weekday 6 from monday)
                ForwardAMonth = ForwardMonth + 2 'add 2 days
            Case 7 'if sunday (weekday 7 from monday)
                ForwardAMonth = ForwardAMonth + 1 'add 1 day
        End Select
    End Function
    Create a module in your db, paste the code.

    You can now call it from anywhere using
    ForwardAMonth([date borrowed])

    Tip: Dont use spaces in any names of anything in a DB!

    Regards
    hey thanks, this really has been useful
    i appreciate your help!

Posting Permissions

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