Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    3rd Week of the Month (Access 97)

    Hi

    Based on the current date I need to determine the date for the Monday in the third week of next month. I can't think of a simple of way of doing this (a quick check of the Microsoft site did not reveal an answer). However, I'm sure there is and it is bound to have been posted here. So a pointer in the right direction would be much appreciated.

    Thanks & Regards
    WTH

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 3rd Week of the Month (Access 97)

    I hope that he following VBA fucntion (to be put in a standard module) can help:

    Function DayInMonth(aYear As Long, aMonth As Long, aDay As Long, aNum As Long) As Date
    If aNum = 9 Then
    DayInMonth = DayInMonth(aYear, aMonth + 1, aDay, 1) - 7
    Else
    DayInMonth = DateSerial(aYear, aMonth, _
    7 * aNum + 1 - Weekday(DateSerial(aYear, aMonth, 1), aDay Mod 7 + 1))
    End If
    End Function

    The will return the aNum-th day of type aDay (Sunday = 1, Monday = 2, ..., Saturday = 7) in the specified year aYear and month aMonth. aNum can be 1, 2, 3, 4, or 9 for the last day of the specified type. To calculate the 3rd Monday in next month, use

    DayInMonth(Year(Date()), Month(Date()) + 1, 2, 3)

    If you would like to avoid the VBA function, use this expression:

    DateSerial(Year(Date()),Month(Date()) + 1, 22 - WeekDay(DateSerial(Year(Date()), Month(Date()) + 1, 1), 3))

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 3rd Week of the Month (Access 97)

    Hi Hans

    Many thanks (and it saved me a heap of time). Either method is acceptable to me.

    Regards
    WTH

Posting Permissions

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