Do you always want the date to be the "next" 4th Wednesday of the month, or
perhaps the last Wednesday? Some months have 5?
I shall assume 4th wed of the month.
The way to set this up is to use the forms before-insert event.
(this event ONLY fires if the user starts typing into the record - but, if
the user looks at the blank record, and closes the form, then you don't get
a blank record added..so, it a good spot....
Assuming our control paymentdate is set to "date", then we can go:
dim dtTempDate as date
if isnull(me.PaymentDate) = false then
dtTempdate = Get4Wed(me.PaymentDate)
if me.Paymentdate dtTempDate then
' we after the last month, so
' jump ahead
dtTempdate = DateSerial(Year(dtDate), Month(dtDate) + 1, 1)
dtTempDate = Get4Wed(dtTempDate)
end if
me.meetingDate = dtTempDate
end if
I also wrote a get 4th wed function for a give date value..it follows my sig
Some of the better developers here could likely write out the whole thing as
a SINGLE expression, but my brain just don't work that way!!!
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
paste the follwing code into a standard code module..make sure it compiles
also...
Public Function Get4Wed(dtDate As Date) As Date
Dim intFirstW As Integer
Dim dtFirst As Date
Dim dtFirstW As Date
' get 1st day of this month
dtFirst = DateSerial(Year(dtDate), Month(dtDate), 1)
' get 1st wed of this month
intFirstW = 4 - Weekday(dtFirst)
If intFirstW < 0 Then
intFirstW = intFirstW + 7
End If
dtFirstW = dtFirst + intFirstW
dtFirstW = dtFirstW + 21
Get4Wed = dtFirstW
End Function