Wrox Programmer Forums
|
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
 
Old February 18th, 2005, 04:59 PM
Authorized User
 
Join Date: Feb 2005
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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!

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

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

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

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
 
Old February 18th, 2005, 09:00 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

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

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

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

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

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

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

dartcoach

 
Old February 23rd, 2005, 05:55 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old February 23rd, 2005, 06:00 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

Kevin

 
Old February 24th, 2005, 08:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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!

How about

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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.