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

1. 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. 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. 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
•