Wrox Programmer Forums Date of birth question
 |
 Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

February 18th, 2005, 04:59 PM
 satts Authorized User Join Date: Feb 2005 Posts: 30 Thanks: 0 Thanked 0 Times in 0 Posts
Date of birth question

Hi all, i cant believe how helpful everyone is on here. Thanks. I was wondering if anyone could help with the following. I have a members table, one of the rows is a date of birth. I want the age to automatically come up in the next row(age)when the user has inputed the date of birth. The reason for this is: I need to create reports for under 17, over 18 year olds and all members. HELP!

February 18th, 2005, 06:40 PM
 dartcoach Friend of Wrox Join Date: Jan 2005 Posts: 471 Thanks: 0 Thanked 1 Time in 1 Post

Satts,
You don't want to store the age on a table. You should calculate each time, since it changes based on todays date in reference to the birth date.
There is a datediff function which will calculate the age.
It looks like this:

Age = DateDiff("yyyy",[Birthdate],Now())

This gives you the age - in years based on the birthdate.

Good Luck,

Kevin

February 18th, 2005, 07:40 PM
 dlamarche Authorized User Join Date: Feb 2005 Posts: 34 Thanks: 0 Thanked 0 Times in 0 Posts

Hi Satts. The expression given by dartcoach will return the age the person will be THIS YEAR. What if the person's birthday had not yet come but it is coming soon this current year. This function would not return exactly what you need. In other words if the person would be 35 in 45 days the expression would return 35 and not 34 (which is more accurate).

The following expression is more complex but is acurate to the day which is what you need.

DateDiff("yyyy",[BirthDate],Date)+Cint(DateSerial(Year(Date),Month([BirthDate]),day([BirthDate]))>Date)

For example in the debug window (Ctrl+G) paste the following and hit Enter. It uses my date of birth. My birthday is in less than a year from today:

?datediff("yyyy",#08/30/56#,Date)+cint(dateserial(year(date),month(#08/30/56#),day(#08/30/56#))>date)

It will return 48 (which is true). The previous expression would have returned 49 (which is the age I will be on this year's birthday).

Want to know how it works? Read on it is more simple than it look.
The first part is dartcoach expression:

?datediff("yyyy",#08/30/56#,Date) which will return 49

Then the expression adds to 49 the result of another expression. The expression is made of two part. The first part it uses DateSerial to recreate this years birthday:

DateSerial(Year(Date),Month(#08/30/56#),Day(#08/30/56#)

Which yields 08/30/2005 (Aug 30th 2005)

Then it compare that date with today's date:

08/30/2005 > Date

If my birthday (this year) is later this year the above expression returns True otherwise it returns False. So in other words it all adds up to the following:

[The age I will be this year]+[Has my bday passed yet or not]

Do you know the numeric value of True and False in VBA? Well True is equal to -1 (minus 1) and False equals 0 (zero). So :

[My age this year]+ -1 if my birthday has passed (48)
[My age this year]+ 0 if my birthday is still ahead (49)

Paste this in the Debug window:
?49+Int(#08/30/2005# > Date) And hit Enter.

Hope this helped and was clear!

Daniel

February 18th, 2005, 09:00 PM
 dartcoach Friend of Wrox Join Date: Jan 2005 Posts: 471 Thanks: 0 Thanked 1 Time in 1 Post

Daniel,

You are more than correct. I forgot about rounding when I replied.
The following isn't as complex as your code, and probably not as efficient, but might be easier to understand.

Age = DateDiff("yyyy", Birtdate, Now())
' check to see if birthday is still coming this year.
If DatePart("m", Birthdate) > DatePart("m", Now()) Then
Age = Age - 1
Else
' check to see if their birthday is the current month.
If DatePart("m", Birthdate) = DatePart("m", Now()) Then
' check to see if their birthday is later this month.
If DatePart("d", Birthdate) > DatePart("d", Now()) Then
Age = Age - 1
End If
End If
End If

This does work. Like I say, it's probably not efficient.

dartcoach

February 19th, 2005, 07:39 AM
 satts Authorized User Join Date: Feb 2005 Posts: 30 Thanks: 0 Thanked 0 Times in 0 Posts

Thanks for your help!!! I am new to all this and find it really hard to understand all the code.

February 19th, 2005, 07:40 AM
 satts Authorized User Join Date: Feb 2005 Posts: 30 Thanks: 0 Thanked 0 Times in 0 Posts

using the above codes, does the age automatically change every year?

February 19th, 2005, 04:13 PM
 dartcoach Friend of Wrox Join Date: Jan 2005 Posts: 471 Thanks: 0 Thanked 1 Time in 1 Post

The above code is based on the difference between the birthdate and the systems current date. It will recalculate everytime.

dartcoach

February 23rd, 2005, 05:55 PM
 SerranoG Friend of Wrox Join Date: Jun 2003 Posts: 1,151 Thanks: 2 Thanked 14 Times in 14 Posts

Wouldn't this work, too?

Age = DateDiff("yyyy", Birtdate, Date()) + (Date() < Birthdate)

The second half (after the plus) evaluates to TRUE (i.e. -1) if the date is before the birthday. Therefore, you'd get the years minus one. If the date is >= the birthdate then the second half evaluates to FALSE (i.e. zero).

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

February 23rd, 2005, 06:00 PM
 dartcoach Friend of Wrox Join Date: Jan 2005 Posts: 471 Thanks: 0 Thanked 1 Time in 1 Post

Greg,
I don't know. If you compare Date() to Birthdate, Date() will always be greater because you're comparing the whole date.

Kevin

February 24th, 2005, 08:43 AM
 SerranoG Friend of Wrox Join Date: Jun 2003 Posts: 1,151 Thanks: 2 Thanked 14 Times in 14 Posts

Quote:
 quote:Originally posted by dartcoach  If you compare Date() to Birthdate, Date() will always be greater because you're comparing the whole date.
You know... you're absolutely right. I forgot about the year! Yikes!

Age = DateDiff("yyyy", Birtdate, Date()) + _
(Date() < DateSerial(Year(Date()), Month(Birthdate), Day(Birthdate)))

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

 Similar Threads Thread Thread Starter Forum Replies Last Post Code for date of Birth.... aaronabdiel Visual Studio 2005 1 January 2nd, 2008 03:25 AM Birth Date listings xp_mahesh JSP Basics 0 June 2nd, 2006 12:57 AM Validation for Date of Birth crmpicco Javascript How-To 2 June 30th, 2005 08:13 AM validating date of birth in asp shoakat Classic ASP Databases 7 September 3rd, 2004 12:18 AM Date() question Tangerine Classic ASP Components 2 March 3rd, 2004 04:14 AM