calculating the Nth weekday in the month.

[Mpatt]

Hi all,
I've inherited a database which is used to track tape mounts, the table currently has a field for

date last used
number of days in cycle
date of next use (arrived at by adding the above 2 values which is done in an update query)

I've now been asked to add tapes that will be loaded on the 3rd sunday of the month so the days in the cycle will be variable but I need to work out the date of next use for these tapes.

Can anyone give me any pointers to usefull sources that will help me figure this out? I'm not looking for someone to write a solution but any hints about where to start will be helpfull - are there any functions which would help me work this out?

TIA for anything!

 

[SgtJarrow]

Credit goes to original poster, although I can't remember who it was....



Function NthDayOfMonth(intMonth As Integer, intYear As Integer, intOccurrence As Integer, intDay As Integer) As Variant
' Comments : Returns the date of the Nth day (Monday, Tuesday, etc.) of the month
' Parameters: intMonth - month to check
' intYear - year to check
' intOccurrence - the occurrence number of the day to calculate
' (1 for first, 2 for second, etc.)
' intDay - the day of the week to calculate (1 for Sunday, 2 for Monday, etc.)
' Returns : Nth day of month (null if date does not exist)
'
Dim varTempDate As Variant
Dim intCurrDay As Integer

varTempDate = DateSerial(intYear, intMonth, 1)

If (intDay > 0) And (intDay < 8) And (intOccurrence > 0) Then
' Calculate first intDay of the month.
intCurrDay = Weekday(varTempDate)
If intCurrDay <> intDay Then
If intCurrDay < intDay Then
varTempDate = varTempDate + (intDay - intCurrDay)
Else
varTempDate = varTempDate + (7 + intDay - intCurrDay)
End If
End If
If intOccurrence > 1 Then
varTempDate = varTempDate + 7 * (intOccurrence - 1)
If Month(varTempDate) <> intMonth Then ' Date goes past month
varTempDate = Null
End If
End If
Else
varTempDate = Null
End If

NthDayOfMonth = varTempDate

End Function


=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com