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