???? Do you want the 2nd Thursday of this month if it has not yet occurred,
otherwise the second thursday of next month
I think the following MIGHT work for you
STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.
UPDATE YourTable
SET YourDateField =
IIF(fDayInMonth(2,5,Month(Date()),Year(Date())>Date()
,fDayInMonth(2,5,Month(Date()),Year(Date())
,fDayInMonth(2,5,Month(DateAdd("m",1,Date())),Year(DateAdd("m",1,Date())))
WHERE YourDateField < Date()
You will need to save the following function into a VBA module, so you can
call it in your query
Public Function fDayInMonth(WeekNumber As Integer, Wkday As Integer, _
dMonth As Integer, dYear As Integer) As Date
Dim FirstOfMonth As Date
Dim NextWeekDay As Integer
Dim RootDate As Date
FirstOfMonth = DateSerial(dYear, dMonth, 1)
NextWeekDay = IIf(Wkday = vbSaturday, vbSunday, Wkday + 1)
RootDate = FirstOfMonth - Weekday(FirstOfMonth, NextWeekDay)
fDayInMonth = RootDate + WeekNumber * 7
End Function
To get the date of the 3rd thursday in september 1965 , use
DayInMonth(3, vbThursday, 9, 1965)
Did this help?
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
