Help with finding a date ??

I have two date fields.
PaymentDate - is set to default to today : =Date()
MeetingDate - is going to find the next meeting date after the PaymentDate

So using April as an example:

If today's date is 4/22/2008 , I want the MeetingDate field to look up the
NEXT meeting this month... this would show as 4/23/2008.
If today's date is 4/29/2008, then I need the query to find the NEXT meeting
date which would be 5/28/2008.
So the logic is :
If (Today is less than the fourth wednesday of current month, Then show the
fourth of this month, Else show the next fourthof the month)

How can I have this meeting date pre-fill like this?

Re: Help with finding a date ??

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
pleaseNOOSpamKallal@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