By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,454 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Calculating next x anniversary dates

P: 6
I need to query a table about next x anniversary dates, based on birthdate.
Can someone teel me how to do it?
Thanks.
Nov 24 '09 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. SELECT DOB, 
  2.        DateSerial(Year(Date()) + IIf(Month([DOB])*100+Day([DOB])<=Month(Date())*100+Day(Date()),1,0),
  3.                   Month([DOB]),
  4.                   Day([DOB])) AS Anniversary
  5. FROM tblDOB;
  6.  
Test Data

Expand|Select|Wrap|Line Numbers
  1. DOB          Anniversary
  2. 25/06/1957   25/06/2010
  3. 25/12/1967   25/12/2009
  4. 01/01/2009   01/01/2010
  5. 29/02/2008   01/03/2010
  6. 14/06/1986   14/06/2010
  7. 14/12/2007   14/12/2009
  8. 08/12/2007   08/12/2010
  9.  
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

Share this Question
Share on Google+
17 Replies


topher23
Expert 100+
P: 234
I'm not sure what you mean by anniversaries based on birthdates. Can you clarify that?
Nov 25 '09 #2

P: 6
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.
Nov 25 '09 #3

topher23
Expert 100+
P: 234
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT UserName, UserBirthday FROM tblUsers 
  2. 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.

Expand|Select|Wrap|Line Numbers
  1.  SELECT UserName, UserBirthday FROM tblUsers 
  2. 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.
Nov 25 '09 #4

100+
P: 675
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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 26 '09 #5

topher23
Expert 100+
P: 234
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.
Nov 26 '09 #6

topher23
Expert 100+
P: 234
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:

Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 26 '09 #7

NeoPa
Expert Mod 15k+
P: 31,273
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.
Nov 30 '09 #8

NeoPa
Expert Mod 15k+
P: 31,273
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().
Nov 30 '09 #9

topher23
Expert 100+
P: 234
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.

Expand|Select|Wrap|Line Numbers
  1. expAnniversary: 
  2. IIf(CDate(Month([UserBirthday])<Month(Now()),
  3. CDate(Left(CStr([UserBirthday]),InstrRev(CStr([UserBirthday]),"/")) & Year(DateAdd("yyyy",1,Now()))),
  4. 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.
Nov 30 '09 #10

100+
P: 675
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!
Nov 30 '09 #11

NeoPa
Expert Mod 15k+
P: 31,273
Fair enough OB. It never hurts to drop these tidbits of info around though I find ;)

y2.1k - What are you like :D
Nov 30 '09 #12

P: 6
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.
Dec 7 '09 #13

P: 6
Hi,

This is how I did it:
Expand|Select|Wrap|Line Numbers
  1. expAnniversary: IIf(CDate(Day([UserBirthday]) & "-" &
  2.                     Month([UserBirthday]) & "-" &
  3.                     Year(Now()))<Date();
  4.                     CDate(Day([UserBirthday]) & "-" &
  5.                     Month([UserBirthday]) & "-" &
  6.                     Year(Now())+1);
  7.                     CDate(Day([UserBirthday]) & "-" & 
  8.                     Month([UserBirthday]) & "-" & 
  9.                     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.
Dec 8 '09 #14

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT DOB, 
  2.        DateSerial(Year(Date()) + IIf(Month([DOB])*100+Day([DOB])<=Month(Date())*100+Day(Date()),1,0),
  3.                   Month([DOB]),
  4.                   Day([DOB])) AS Anniversary
  5. FROM tblDOB;
  6.  
Test Data

Expand|Select|Wrap|Line Numbers
  1. DOB          Anniversary
  2. 25/06/1957   25/06/2010
  3. 25/12/1967   25/12/2009
  4. 01/01/2009   01/01/2010
  5. 29/02/2008   01/03/2010
  6. 14/06/1986   14/06/2010
  7. 14/12/2007   14/12/2009
  8. 08/12/2007   08/12/2010
  9.  
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
Dec 8 '09 #15

NeoPa
Expert Mod 15k+
P: 31,273
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.
Dec 9 '09 #16

topher23
Expert 100+
P: 234
I've never seen a practial use of DateSerial before - that's quite elegant. Thanks, Stewart!
Dec 9 '09 #17

NeoPa
Expert Mod 15k+
P: 31,273
A new question (Hijack) was posted in here but has now been moved to Calculating Anniversary Date.
1 Week Ago #18

Post your reply

Sign in to post your reply or Sign up for a free account.