This code was generously donated by Bob Askew (raskew) from Access World Forums and modified by Bob Larson to be able to look either forward or back.
NOTE: If you use this in a QUERY or Control Source, you have to specify the day NUMBER instead
of vbWednesday, vbSunday, etc. because queries and control sources do not know about the VBA
constants.
Function fNextNthDay(dteStart As Date, _
intWeekday As Integer, Optional blnPrevious As Boolean) As Date
'************************************************* *
'Purpose: Round date up to next specified
' weekday
' The optional parameter blnPrevious specifies if you want
' the PREVIOUS date. The default is to get the NEXT date.
'Inputs:
' 1) ? fNextNthDay(#4/18/06#, vbWednesday)
' 2) ? fNextNthDay(#4/19/06#, vbWednesday)
' 3) ? fNextNthDay(#4/20/06#, vbWednesday)
' 4) ? fNextNthDay(#4/19/06#, vbWednesday, True)
' 5) ? fNextNthDay(#4/20/06#, vbWednesday, True)
'Output:
' 1) 4/19/06
' 2) 4/19/06
' 3) 4/26/06
' 4) 4/19/06
' 5) 4/19/06
'************************************************* *
If blnPrevious Then
fNextNthDay = (dteStart - Weekday(dteStart) + _
intWeekday + _
IIf(Weekday(dteStart) < intWeekday, -7, 0))
Else
fNextNthDay = dteStart - Weekday(dteStart) + _
intWeekday + _
IIf(Weekday(dteStart) > intWeekday, 7, 0)
End If
End Function