Calculate Age from Date of Birth

  • Thread starter Thread starter KirstieA
  • Start date Start date
K

KirstieA

Can someone please tell me how i can calculate someone's age on a form or
report?

I have a date of birth field, called [DOB]

None of the calculations i have done have worked! I can do it easily in
excel, why not access?!!

TIA,

Kirstie
 
KirstieA said:
Can someone please tell me how i can calculate someone's age on a
form or report?

I have a date of birth field, called [DOB]

None of the calculations i have done have worked! I can do it easily
in excel, why not access?!!
Click to expand...

The trick is to take into account whether the birthday has occurred in the
current year yet.


Age = DateDiff("yyyy", [DOB], Date()) + Not (Format(Date(), "y") >=
Format([DOB], "y"))
 
This calculation is accurate about half the time.

--
Duane Hookom
MS Access MVP


Klatuu said:
= Datediff("yyyy",[DOB], date)

KirstieA said:
Can someone please tell me how i can calculate someone's age on a form or
report?

I have a date of birth field, called [DOB]

None of the calculations i have done have worked! I can do it easily in
excel, why not access?!!

TIA,

Kirstie
Click to expand...
Click to expand...
 
Duane,

Is there a more precise way of doing this?

Kirstie
Duane Hookom said:
This calculation is accurate about half the time.

--
Duane Hookom
MS Access MVP


Klatuu said:
= Datediff("yyyy",[DOB], date)

KirstieA said:
Can someone please tell me how i can calculate someone's age on a form
or
report?

I have a date of birth field, called [DOB]

None of the calculations i have done have worked! I can do it easily in
excel, why not access?!!

TIA,

Kirstie
Click to expand...
Click to expand...
Click to expand...
 
Yes, read Rick Brandt's reply or check this link
http://www.mvps.org/access/datetime/date0001.htm.

Using DateDiff() returns the number of date interval boundaries that have
been passed. For instance:
DateDiff("yyyy",#12/31/2004#,#1/1/2005#) = 1
DateDiff("m",#12/31/2004#,#1/1/2005#) = 1
DateDiff("d",#12/31/2004#,#1/1/2005#) = 1

--
Duane Hookom
MS Access MVP


KirstieA said:
Duane,

Is there a more precise way of doing this?

Kirstie
Duane Hookom said:
This calculation is accurate about half the time.

--
Duane Hookom
MS Access MVP


Klatuu said:
= Datediff("yyyy",[DOB], date)

:

Can someone please tell me how i can calculate someone's age on a form
or
report?

I have a date of birth field, called [DOB]

None of the calculations i have done have worked! I can do it easily in
excel, why not access?!!

TIA,

Kirstie
Click to expand...
Click to expand...
Click to expand...
 
Kirstie,

Try it like this...

=DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmdd"))
 
Kirstie,

Try it like this...

=DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmd
d"))
Click to expand...

I have this subroutine that I use in a VB application that will calculate
the exact age of someone in years, months and days (ie. 47 yrs, 8 months, 8
days). One of these days, I will convert it to VBA function, but since I
have not needed it yet, I haven't done this. Here is the code if anyone
wants to give it a try:

Public Sub actage()
Dim intBmonth As Integer, intCmonth As Integer
Dim intNumMonths As Integer, intNumYears As Integer
intNumMonths = DateDiff("m", intBdate, intDay)
intNumYears = DateDiff("yyyy", intBdate, intDay)
intBmonth = Month(intBdate) 'birth month
intCmonth = Month(intDay) 'current month
'calculate number of years
intNumYrs = intNumYears 'if birthdate is before current date
If intBmonth > intCmonth Then
intNumYrs = intNumYrs - 1 'havent reached the birthday yet
End If
If intBmonth = intCmonth And Day(intBdate) > Day(intDay) Then
intNumYrs = intNumYrs - 1 'havent reached birthday yet
End If
'calculate number of months
If (intBmonth = intCmonth) And (Day(intDay) < Day(intBdate)) Then
intNumMts = 11 'same month but not yet birthdate
Else
If (intBmonth = intCmonth) And (Day(intDay) >= Day(intBdate)) Then
intNumMts = 0 'same month but after birthdate
Else
If Day(intBdate) <= Day(intDay) Then
intNumMts = intNumMonths Mod 12 'different month but after
birthday
Else
intNumMts = intNumMonths Mod 12 - 1 'different month but before
birthday
End If
End If
End If
If Day(intDay) >= Day(intBdate) Then 'birthdate is before current date
intNumDys = Day(intDay) - Day(intBdate)
Else 'birthdate is after current date, get remaining days of month
' 30 days has sept, april june and november, all the rest have 31,
except february
Select Case intBmonth
Case 4, 6, 9, 11
intNumDys = 30 + Day(intDay) - Day(intBdate)
Case 1, 3, 5, 7, 8, 10, 12
intNumDys = 31 + Day(intDay) - Day(intBdate)
Case 2
intNumDys = 28 + Day(intDay) - Day(intBdate)
End Select
End If

End Sub


NOTE: any variable that is not specifically dimensioned in this routine has
been declared in as public variables in a module.
the results are returned to the following three variables:
intnumdys, intnummts, intnumyrs.

Good luck.
Steve
 
Steven Greenberg said:
Kirstie,

Try it like this...

=DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmd
d"))
Click to expand...

I have this subroutine that I use in a VB application that will calculate
the exact age of someone in years, months and days (ie. 47 yrs, 8 months,
8
days). One of these days, I will convert it to VBA function, but since I
have not needed it yet, I haven't done this. Here is the code if anyone
wants to give it a try:
Click to expand...


Take a look at what I have at
http://www.accessmvp.com/djsteele/Diff2Dates.html if you want a function
that does this.
 
Hi,


The first of March has a different "y" value in lap year than in not lap
year.

? DatePart( "y", #03-01-1996#), DatePart( "y", #03-01-1997#), DatePart( "y",
#03-01-1998#), DatePart( "y", #03-01-1999#)
61 60 60 60


If you prefer, someone born the First of March of 1996 may find funny if you
celebrates his/her birthday the Second of March, 3 years out of 4.
Format( ... , "mmdd") , or preferable, DateSerial(Year(now), ..., .... ),
are among the few simple tests that work.


Sure, I hope you don't ask me to define what is a "simple test" :-)



Vanderghast, Access MVP



Rick Brandt said:
KirstieA said:
Can someone please tell me how i can calculate someone's age on a
form or report?

I have a date of birth field, called [DOB]

None of the calculations i have done have worked! I can do it easily
in excel, why not access?!!
Click to expand...

The trick is to take into account whether the birthday has occurred in the
current year yet.


Age = DateDiff("yyyy", [DOB], Date()) + Not (Format(Date(), "y") >=
Format([DOB], "y"))
Click to expand...
 
Back
Top