<La*******@gmail.comwrote in message
news:11**********************@o61g2000hsh.googlegr oups.com...
Greetings! I have a database in access that contains information
about the date members joined the company. Every year, membership
must be renewed for each member. Usually I will send out a standard
letter from a report to each member regardless of the date they
joined. Members are only supposed to receive letters an exact year
after they joined. I am wondering if it is possible for me to do a
setting that will let me to create reports and send letters that will
be a year from the exact date a member joined.
The following code appears to accurately calculate the next occurrence of a
recurring date... see the comments for details. You could use it in a Query
and set Criteria using today's Date and the DateAdd function to set a
"window" in which dates would qualify for selection. You might want a
report of all memberships which will need to be renewed within the next 15
days, or 30 days, or other time period for a mailing (or e-mailing).
It has error code, which you may want to change, and has been "lightly
tested" for a demonstration, but not "rigorously tested" as for production.
Written and tested in Access 2003. Use it at your own risk...
Function NextOccurrenceOfDate(pdatDate As Date) As Date
'---------------------------------------------------------------------------------------
' Procedure : NextOccurrenceOfDate
' DateTime : 6/15/2007 23:25
' Author : LARRY LINSON
' Purpose : To calculate next occurrence of a recurring date, e.g.,
' a birthday, anniversary, etc.
' If month and day are less than current date, then the
' next occurrence is next year
' If month and day are equal to current date, this is "next"
' If month and day are greater than current date, then the
' next occurrence is this year
'---------------------------------------------------------------------------------------
'
On Error GoTo PROC_Error
Dim intMonth As Integer
Dim intDay As Integer
Dim intYear As Integer
intMonth = Month(pdatDate)
intDay = Day(pdatDate)
intYear = Year(pdatDate)
If intMonth = Month(Date) Then 'This month, so must test day
If intDay = Day(Date) Then 'Current/next occurrence is
today
NextOccurrenceOfDate = Date
ElseIf intDay Day(Date) Then 'Day is yet to come -- this year
NextOccurrenceOfDate = DateSerial(Year(Date), intMonth, intDay)
Else 'Day is past -- next year
NextOccurrenceOfDate = DateSerial(Year(Date) + 1, intMonth,
intDay)
End If
ElseIf intMonth Month(Date) Then 'Month still to occur this year
NextOccurrenceOfDate = DateSerial(Year(Date), intMonth, intDay)
Else 'Month already occurred this
year
NextOccurrenceOfDate = DateSerial(Year(Date) + 1, intMonth, intDay)
End If
PROC_Exit:
Exit Function
PROC_Error:
On Error GoTo 0
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
NextOccurrenceOfDate of Module basMiscAndEtc"
Resume PROC_Exit:
End Function
Larry Linson
Microsoft Access MVP
