Monthly and Yearly Expiration Query - HELP

[Adam]

I have a date field that holds when my customers first joined my club.
I'm trying to
1. Run a report daily (this part i got in a macro that runs everyday), that
shows who is approaching the monthly time period and is 7 days a way from the
day they started (ie. started on 7th, so want to know the 7th of each month
for this person is approaching).
2. The 2nd part is I'm trying to display an expiration period after 4, 9, 1
year terms. The formula I'm using works if the expiration is within the same
year, but it does not work if someone started in November and crosses over
into the next year of one of the above terms.
Can someone help with these??

 

[John Spencer]

For the first problems the solution might be

Select PersonID
FROM SomeTable
WHERE DateSerial(Year(Date()), Month(Date()),Day(JoinDate)) Between
Date()-4 and Date()


SELECT PersonId
, DateAdd("yyyy",1,JoinDate) as 1Year
, DateAdd("yyyy",4,JoinDate) as 4Year
, DateAdd("yyyy",9,JoinDate) as 9Year
FROM SomeTable




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================