Calculate Age from Date of Birth

[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

 

[Guest]

= Datediff("yyyy",[DOB], date)

 

[Rick Brandt]

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?!!

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"))

 

[Duane Hookom]

This calculation is accurate about half the time.

--
Duane Hookom
MS Access MVP

= 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

 

[MA]

This calculation is accurate about half the time.

You mean that the ladies don't tell the true about theire age?

--
_ _
Ciao
MAssimiliano Amendola www.accessgroup.it
Cisa - Conferenza Italiana per Sviluppatori Access
Info: www.donkarl.com/it

 

[KirstieA]

Duane,

Is there a more precise way of doing this?

Kirstie

This calculation is accurate about half the time.

--
Duane Hookom
MS Access MVP

= 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

 

[Duane Hookom]

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

Duane,

Is there a more precise way of doing this?

Kirstie

This calculation is accurate about half the time.

--
Duane Hookom
MS Access MVP

= 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

 

[Steve Schapel]

Kirstie,

Try it like this...

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

 

[Steven Greenberg]

Kirstie,

Try it like this...

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

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

 

[Douglas J. Steele]

Kirstie,

Try it like this...

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

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:

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

 

[Michel Walsh]

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

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?!!

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"))