RENEW DATE!
Lady...@gmail.com
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.
I will appreciate help in this
Regards,
L.S
Tom van Stiphout
I might want to print the letter only for those members where
MonthsMember Mod 12 = 0
Mod is the Modulo operator - see help file.
MonthsMember can be calculated with the DateDiff function:
DateDiff("m", DateJoined, Date())
This will give you the number of months that the person is a member.
I'm assuming DateJoined is the date field holding the date the person
became a member. Date() returns today's date.
Armed with the above you should be able to create the query for the
report.
-Tom.
Larry Linson
<Lady...@gmail.com> wrote in message
news:1183732153.4...@o61g2000hsh.googlegroups.com...
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
CDMAP...@fortunejames.com
Lady Sugar,
Maybe try the following one-liner:
http://groups.google.com/group/comp.databases.ms-access/msg/f087c672614ca91c
O.K., I just tried it. It needs more than one line to handle Null
AnniversaryDate's plus needs varAnniversary in place of dtAnniversary
in one spot :-):
'---Begin Module Code---
Public Function boolAnniversaryInRange(dtStart As Date, dtEnd As Date,
varAnniversary As Variant) As Boolean
boolAnniversaryInRange = False
If IsNull(varAnniversary) Then Exit Function
boolAnniversaryInRange = (Int(Format(DateAdd("d", -1, dtStart),
"yyyy.mmdd") - Format(varAnniversary, "yyyy.mmdd")) <
Int(Format(dtEnd, "yyyy.mmdd") - Format(varAnniversary, "yyyy.mmdd")))
End Function
'---End Module Code---
I used:
tblAnniversary
AID AutoNumber
AnniversaryDate Date/Time
AID AnniversaryDate
1 4/9/2006
2 Null
3 2/3/20003
4 7/3/2004
5 7/18/2005
qryAnniversary:
SELECT * FROM tblAnniversary WHERE
boolAnniversaryInRange(DateSerial(Year(Date()), Month(Date()), 1),
DateSerial(Year(Date()), Month(Date()) + 1, 0), [AnniversaryDate]) =
-1;
!qryAnniversary:
AID AnniversaryDate
4 7/3/2004
5 7/18/2005
I.e., it returns all records where the AnniversaryDate comes up within
the current month. If you want all the rows where the anniversary
comes up on a specific date, make dtStart and dtEnd the same:
qryAnniversary:
SELECT * FROM tblAnniversary WHERE boolAnniversaryInRange(#7/3/07#,
#7/3/07#, [AnniversaryDate]) = -1;
!qryAnniversary:
AID AnniversaryDate
4 7/3/2004
Note: The function does not care about what year is contained in the
AnniversaryDate and should also work across year-end boundaries.
Another note: The format "yyyy.mmdd" does not return a true fraction
of the year, but the relative values of "yyyy.mmdd" compare the same
as the true fractions of a year from which they are obtained. This is
an example where the properties of partially ordered sets (posets)
come to the rescue. Replace tblAnniversary with the name of the table
you are using and replace AnniversaryDate with the name of the field
you are using for your anniversary date. Watch for line wrap of the
code. Be sure to run enough tests to be sure that the code works
correctly. The query can also read dtStart and dtEnd from values on a
form (just make sure they exist before running the query).
James A. Fortune
CDMAP...@FortuneJames.com
Lady Sugar
> On Jul 6, 10:29 am, LadySu...@gmail.com wrote:
>
> > 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.
> > I will appreciate help in this
>
> > Regards,
> > L.S
>
> Lady Sugar,
>
> Maybe try the following one-liner:
>
Thanks ALL for the speedy response! i am going to try it.