Calculating next x anniversary dates

[apsf68]

I need to query a table about next x anniversary dates, based on birthdate.
Can someone teel me how to do it?
Thanks.

[topher23]

I'm not sure what you mean by anniversaries based on birthdates. Can you clarify that?

[apsf68]

Ok.
I have a table with a lot of users, and I want to querie that table to know who are the usersthat will have her birthday in the next x days.
My english isn't very good, but I hope it's good enought to let you understand what I need.
Thanks.

[topher23]

Okay, what you're looking for here is a select query with some criteria. Not knowing the names of your table fields, I'll just mock someting up:

SELECT UserName, UserBirthday FROM tblUsers 
WHERE UserBirthday Between Date() And DateAdd("d",7,Date());

This SQL statement creates a query that selects the UserName and UserBirthday fields from a table called tblUsers, and only shows the users who have birthdays between today and 7 days from today.

To use a field on a form to do this, I'll reference a form called frmBirthdays with a text box called txtEndDate.

 SELECT UserName, UserBirthday FROM tblUsers 
WHERE UserBirthday Between Date() And Forms!frmBirthdays!txtEndDate;

Now, these are SQL statements. These are what are created when you use the Query Designer. So, in the query designer, you would select your users table, drag in your username and birthday fields, then type 'Between Date() And ...(whichever method you plan to use). You can right-click your query and look at it in SQL View to see the similarity with what I've typed above.

Once you have your query built, you can use it as the record source for a report, a list box, etc.

[OldBirdman]

An anniversary, or a birthday, is a day, NOT a date. An anniversary would be a day in a year. So the 365th day is 31 December most of the time, but 30 December in a Leap Year. I believe this is referred to as a 'Date Serial' in Access.
Most databases store the birth date, say 31 December, 1966. We would have to see if this day, not date, for the year 2009, is within 'x' days from now. As today is 25 November, 2009, x would have to be >=35 in order for this to be true.
But if x is greater than 35, we would be looking for birthdays in January also, which would have date serial values lower than today's date serial value.

DateSerial = Format(DateToConvert, "y")

I do not know how to do this as an SQL solution. Even writing a function isn't that simple because of leap year, even if we ignore the fact that the year 2100 will not be a leap year even though Mod(2100, 4) = 0, which normally denotes a leap year.

[topher23]

I see your point, OB. I need to rethink this with a solution that will work. I don't know what I was thinking earlier.

[topher23]

I've got it this time. This is a very convoluted function that converts the date to a string and appends the current year. Messing with the date functions just caused more trouble than it solved, as OldBirdman pointed out. So now, instead of using the actual UserBirthday field, you'll create a new calculated field, as follows:

expAnniversary: CDate(Left(CStr([UserBirthday]),InstrRev(CStr([UserBirthday]),"/")) & Year(Now()))

Apply the same criteria as posted earlier to this new calculated field and it will work.

[NeoPa]

OB,

The formula for determining Leap Years is a little more involved than that.

A year is Leap if it is divisible by 4, unless it is divisible by 100. Even then, if it is divisible by 400 it is also a Leap year.

The year 2000 was just such an example of the double exception.

[NeoPa]

Topher,

This is nearly there, but it will fail when X extends beyond the current year. I'll let you have a stab at this, but it involves checking if the day (within the year) of the anniversary is less than the day of Date().

[topher23]

I realized that this wouldn't always work as I was looking over this thread this morning. The previous solution was put together hastily as I was on the way out the door for the long US holiday weekend, so I didn't think to account for new year rollovers.

And of course, you (NeoPa) hit the nail right on the head with the way to make this work. apsf68, you'll need to use an IIf statement in your calculated field. If you've never used one of these before, read about it in the VBA help file - it's a very useful function.

This will now create a calculated field using the IIf function to see if the birthday month is less than the current month. If it is, it will append next year's Year to the birth date rather than the current year. This is the most compact method I could come up with for doing this. I'd also hazard a guess that this is pretty much the same logic most of us use to calculate the same thing in our heads.

expAnniversary: 
IIf(CDate(Month([UserBirthday])<Month(Now()),
CDate(Left(CStr([UserBirthday]),InstrRev(CStr([UserBirthday]),"/")) & Year(DateAdd("yyyy",1,Now()))),
CDate(Left(CStr([UserBirthday]),InstrRev(CStr([UserBirthday]),"/")) & Year(Now())))

Obviously, this won't account for birthdays that occurred earlier in the month on a list for next year's birthdays. If you intend to look that far out, I'm sure you can figure out how to modify this code to go down to the date level rather than the month level by checking the calculated date for this year against the current date. Just note that the more functions you use, the slower your query will run.

[OldBirdman]

NeoPa,
I knew that. I entered this thread to point up the difference between a date and an (anniversary) day. The stated problem is to see what occurances in the future exist within an entered range: today to/thru the entered date.
I didn't want to complicate it too much, but wanted to point out that the y2K+100 (y2.1k?) date was an exception. If this code is still running in the year 2200, apsf68 deserves an award for something!

[NeoPa]

Fair enough OB. It never hurts to drop these tidbits of info around though I find ;)

y2.1k - What are you like :D

[apsf68]

Hi everybody,

First of all, thank you very much for your help.
I'm working on a solution I found after reading all your posts.
As soon as I have it developed, I will post it in order to all can use it if someone find it useful.

[apsf68]

Hi,

This is how I did it:

expAnniversary: IIf(CDate(Day([UserBirthday]) & "-" &
                    Month([UserBirthday]) & "-" &
                    Year(Now()))<Date();
                    CDate(Day([UserBirthday]) & "-" &
                    Month([UserBirthday]) & "-" &
                    Year(Now())+1);
                    CDate(Day([UserBirthday]) & "-" & 
                    Month([UserBirthday]) & "-" & 
                    Year(Now())))

Once I have it, is possible to know who is having is anniversary on the next x days.
Hope it could help someone.

[Stewart Ross]

Hi. The anniversary of a birthdate is just the month and day of the birthdate with the current year (if the anniversary has not already occurred this year) or the next year substituted in place of the year of birth. The DateSerial function can be used for this, and has the advantage that it copes with leap year anniversaries of 29 Feb without further adjustment. Assuming table called tblDOB and field called DOB which stores dates of birth:

SELECT DOB, 
       DateSerial(Year(Date()) + IIf(Month([DOB])*100+Day([DOB])<=Month(Date())*100+Day(Date()),1,0),
                  Month([DOB]),
                  Day([DOB])) AS Anniversary
FROM tblDOB;
 

Test Data

DOB          Anniversary
25/06/1957   25/06/2010
25/12/1967   25/12/2009
01/01/2009   01/01/2010
29/02/2008   01/03/2010
14/06/1986   14/06/2010
14/12/2007   14/12/2009
08/12/2007   08/12/2010
 

The IIF expression in the DateSerial part of the formula just adds 1 to the current year if the month and day of the given birthdate have already passed.

-Stewart

[NeoPa]

I've no idea who set the best answer to post #14, but as it's clearly a long way from being so I've reset it.

I would have selected one earlier but there are a number of good answers with some good techniques illustrated.

As Stewart's seems now to be the most succinct I will set that one, but I would certainly comment that Topher's post #10 deserves an Honourable Mention.

[topher23]

I've never seen a practial use of DateSerial before - that's quite elegant. Thanks, Stewart!

[NeoPa]

A new question (Hijack) was posted in here but has now been moved to Calculating Anniversary Date.