Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

wwwxxxAPC, How to unsubscrube from dategen spam, WWWXXXAPC, docmd.close 2585, WWWXXXDOCO, nu vot, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text, how to setup procmail html2text



#1: Help with finding a date ??

Posted on 2008-04-23 00:59:09 by Presto

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?

Report this message

#2: Re: Help with finding a date ??

Posted on 2008-04-23 01:49:54 by PleaseNOOOsPAMMkallal

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, 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 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

paste the follwing code into a standard code module..make sure it compiles

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

Report this message