Question

[Ray1127]

Is there a way in MS Access to calculate how many of a specific day of the week there are in a given month. For example in July 2007 there are 5 mondays and 4 fridays. What I need to do is be able to do is given the week of the month and day of the week I need to calculate the date for that same day in the following month for example the 4th Friday of July is 07/28/2007. the 4th Friday of August is 08/24/2007. I need to be able to calculate the 08/24/2007 date given the 07/28/2007 date. Anybody have an idea?

 

[maxhugen]

Hmmm... whilst writing a function to do that is not difficult, you will run into problems in some cases... eg your date is the 5th Monday in the month, but the next month only has 4 Mondays. What would you do in that case? Revert to the 4th Monday, or what?

Max Hugen
Australia

 

[maxhugen]

Here's an example of how it can be done:

Public Function DateNextMonth(MyDate As Date) As Date
Dim CurrentDay%, CurrentWeekday%, CurrentMonth%, CurrentYear%, CurrentNumberWeekdays%
Dim NextDay%, NextMonth%, NextYear%, NextNumberWeekdays%
Dim i%, DaysInMonth%, v

    CurrentDay = Day(MyDate)
    CurrentWeekday = WeekDay(MyDate)
    CurrentMonth = Month(MyDate)
    CurrentYear = Year(MyDate)
    
    [green]' Get the next month and year[/green]
    If CurrentMonth < 12 Then
        NextMonth = CurrentMonth + 1
        NextYear = CurrentYear
    Else
        NextMonth = 1
        NextYear = CurrentYear
    End If
    
    [green]' Find the occurences of the current weekday[/green]
    For i = 1 To CurrentDay
        v = DateSerial(CurrentYear, CurrentMonth, i)
        If WeekDay(v) = CurrentWeekday Then
            CurrentNumberWeekdays = CurrentNumberWeekdays + 1
        End If
    Next i
    
    [green]' Find how many days are in the next month[/green]
    v = DateAdd("d", -1, DateAdd("m", 1, DateSerial(NextYear, NextMonth, 1)))
    DaysInMonth = Day(v)
    
GetDayOfNextMonth:
    [green]' Find the occurence of the weekday in next month[/green]
    For i = 1 To DaysInMonth
        v = DateSerial(NextYear, NextMonth, i)
        If WeekDay(v) = CurrentWeekday Then
            NextNumberWeekdays = NextNumberWeekdays + 1
            If NextNumberWeekdays = CurrentNumberWeekdays Then
                NextDay = i
                Exit For
            End If
        End If
    Next i
    
    [green]' Check that we did find the weekday, and didn't strike
    ' the issue of "5 Mondays this month but only 4 in next month".[/green]
    ' If that's the case, let's get the prev weekday
    If NextDay = 0 Then
        CurrentNumberWeekdays = CurrentNumberWeekdays - 1
        NextNumberWeekdays = 0
        GoTo GetDayOfNextMonth
    End If
        
    [green]' Return the next month's date[/green]
    DateNextMonth = DateSerial(NextYear, NextMonth, NextDay)
    
End Function

HTH


Max Hugen
Australia

 

[MajP]

Or
To find the occurence of a day in the month

Public Function getDayOccurence(dtmDate As Date) As Integer
  getDayOccurence = ((Day(dtmDate) - 1) \ 7) + 1
End Function

to find the date of an occurence

Public Function getXOccurenceDay(intOccurence As Integer, strDay As String, intMonth As Integer, intYear As Integer) As Date
  Dim tempDay As Date
  tempDay = DateSerial(intYear, intMonth, 1)
  
  Do Until ((getDayOccurence(tempDay) = intOccurence) And (WeekdayName(Weekday(tempDay)) = strDay)) Or (Month(tempDay) > intMonth)
    tempDay = tempDay + 1
  Loop
  If Month(tempDay) > intMonth Then
    MsgBox "There is not an " & intOccurence & " of this day in the given month. Returning the 4th Occurence"
    tempDay = getXOccurenceDay(4, strDay, intMonth, intYear)
  End If
  getXOccurenceDay = tempDay
End Function

demo

Public Sub test()
  Dim dtmDate As Date
  dtmDate = #1/29/2007#
  Debug.Print "day of month = " & Day(dtmDate)
  Debug.Print "day of week value = " & Weekday(dtmDate)
  Debug.Print "day of week name = " & WeekdayName(Weekday(dtmDate))
  Debug.Print "This is the  " & getDayOccurence(dtmDate) & " occurence this month"
  Debug.Print "3rd Sunday of Sept 2007 = "; getXOccurenceDay(3, "Sunday", 9, 2007)
End Sub

output:
day of week value = 2
day of week name = Monday
This is the 5 occurence this month
3rd Sunday of Sept 2007 = 9/16/2007