Calculate someone's age in Access and format the result in years .

[Guest]

I want to calculate someones age calculating the difference between "DOB"
field and today Date() (So its always up to date) I want to display the
result in Years and weeks as accurately as possible, (it influences prices
charged). I want to use an expression in a calculated field in a form, I
cant write VBA (yet!)
The task seems to be converting tenths of a year into weeks. e.g.
=datediff("d",[DOB],Date())/365.25 produces (for example) 63.81. Whats that
in weeks? and Is it accurate?
I'm using Access 2000 (but will soon be switching to 2003)

 

[Douglas J. Steele]

Dividing the difference in days by 365.25 isn't 100% accurate, since there
aren't exactly 365.25 days in a year.

If you use DateDiff to give you weeks, rather than days, and divide by 52,
you'll probably be better off.

 

[Justin Hoffman]

I want to calculate someones age calculating the difference between "DOB"
field and today Date() (So its always up to date) I want to display the
result in Years and weeks as accurately as possible, (it influences prices
charged). I want to use an expression in a calculated field in a form, I
cant write VBA (yet!)
The task seems to be converting tenths of a year into weeks. e.g.
=datediff("d",[DOB],Date())/365.25 produces (for example) 63.81. Whats
that
in weeks? and Is it accurate?
I'm using Access 2000 (but will soon be switching to 2003)

There are many variations on this theme, but if you create a new module and
paste this function in, you will be able to call it from your queries and
forms. It outputs a string such as "36 years 1 month 14 days" but I hope
you can see how to alter it to format the age as you see fit.
If a person's DOB is missing or some other error occurs, it doesn't freak
out - it just returns a blank string.


Public Function AgeToday(Optional DateOfBirth) As String

On Error GoTo Err_Handler

Dim strAge As String
Dim lngDays As Long
Dim lngMonths As Long
Dim lngYears As Long

If IsMissing(DateOfBirth) Then
GoTo Exit_Handler
Else
If Not IsDate(DateOfBirth) Then
GoTo Exit_Handler
End If
End If

lngMonths = DateDiff("m", DateOfBirth, Date)

lngDays = DateDiff("d", DateAdd("m", lngMonths, DateOfBirth), Date)

If lngDays < 0 Then
lngMonths = lngMonths - 1
lngDays = DateDiff("d", DateAdd("m", lngMonths, DateOfBirth), Date)
End If

lngYears = lngMonths \ 12

lngMonths = lngMonths Mod 12

strAge = lngYears & " year"
If lngYears <> 1 Then
strAge = strAge & "s"
End If

strAge = strAge & " " & lngMonths & " month"
If lngMonths <> 1 Then
strAge = strAge & "s"
End If

strAge = strAge & " " & lngDays & " day"
If lngDays <> 1 Then
strAge = strAge & "s"
End If

Exit_Handler:

AgeToday = strAge

Exit Function

Err_Handler:

strAge = ""
Resume Exit_Handler

End Function

 

[Guest]

Justin, thanks for taking time to help! I intend to play with the VBA you
sent and see it work. I think the problem will be that I need a number format
returning to use in a macro that prints a report (ticket to fish) and charges
less when the recipient is under 14 or over 65. Perhaps I can convert the
string to a number. Anyway thanks again.

I want to calculate someones age calculating the difference between "DOB"
field and today Date() (So its always up to date) I want to display the
result in Years and weeks as accurately as possible, (it influences prices
charged). I want to use an expression in a calculated field in a form, I
cant write VBA (yet!)
The task seems to be converting tenths of a year into weeks. e.g.
=datediff("d",[DOB],Date())/365.25 produces (for example) 63.81. Whats
that
in weeks? and Is it accurate?
I'm using Access 2000 (but will soon be switching to 2003)

There are many variations on this theme, but if you create a new module and
paste this function in, you will be able to call it from your queries and
forms. It outputs a string such as "36 years 1 month 14 days" but I hope
you can see how to alter it to format the age as you see fit.
If a person's DOB is missing or some other error occurs, it doesn't freak
out - it just returns a blank string.


Public Function AgeToday(Optional DateOfBirth) As String

On Error GoTo Err_Handler

Dim strAge As String
Dim lngDays As Long
Dim lngMonths As Long
Dim lngYears As Long

If IsMissing(DateOfBirth) Then
GoTo Exit_Handler
Else
If Not IsDate(DateOfBirth) Then
GoTo Exit_Handler
End If
End If

lngMonths = DateDiff("m", DateOfBirth, Date)

lngDays = DateDiff("d", DateAdd("m", lngMonths, DateOfBirth), Date)

If lngDays < 0 Then
lngMonths = lngMonths - 1
lngDays = DateDiff("d", DateAdd("m", lngMonths, DateOfBirth), Date)
End If

lngYears = lngMonths \ 12

lngMonths = lngMonths Mod 12

strAge = lngYears & " year"
If lngYears <> 1 Then
strAge = strAge & "s"
End If

strAge = strAge & " " & lngMonths & " month"
If lngMonths <> 1 Then
strAge = strAge & "s"
End If

strAge = strAge & " " & lngDays & " day"
If lngDays <> 1 Then
strAge = strAge & "s"
End If

Exit_Handler:

AgeToday = strAge

Exit Function

Err_Handler:

strAge = ""
Resume Exit_Handler

End Function

 

[Guest]

Dividing the difference in days by 365.25 isn't 100% accurate, since there
aren't exactly 365.25 days in a year.

If you use DateDiff to give you weeks, rather than days, and divide by 52,
you'll probably be better off.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I want to calculate someones age calculating the difference between "DOB"
field and today Date() (So its always up to date) I want to display the
result in Years and weeks as accurately as possible, (it influences prices
charged). I want to use an expression in a calculated field in a form, I
cant write VBA (yet!)
The task seems to be converting tenths of a year into weeks. e.g.
=datediff("d",[DOB],Date())/365.25 produces (for example) 63.81. Whats
that
in weeks? and Is it accurate?
I'm using Access 2000 (but will soon be switching to 2003)

Douglas, Thanks, I'll try it but I suspect the accuracy will vary. for Example someone who's 65 has had more leap-years than someone who's 14 !

 

[Douglas J. Steele]

Based on what you posted to the other thread in this, I don't see why you
need weeks at all.

There, you said "I need a number format returning to use in a macro that
prints a report (ticket to fish) and charges less when the recipient is
under 14 or over 65". If that's all you care about, isn't knowing their
current age in years sufficient?

To get their current age, use:

DateDiff("yyyy", [DOB], Date()) - _
IIf(Format(Date(), "mmdd") < Format([DOB], "mmdd"), 1, 0)

The second part makes sure to check whether or not they've had their
birthday this year, and adjusts the results of DateDiff accordingly.

If you really do need week accuracy, you can determine the number of weeks
between today and their birthday this year, where their birthday this year
will be DateSerial(Year(Date()), Month([DOB]), Day([DOB])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas, Thanks, I'll try it but I suspect the accuracy will vary. for
Example
someone who's 65 has had more leap-years than someone who's 14 !

Dividing the difference in days by 365.25 isn't 100% accurate, since
there
aren't exactly 365.25 days in a year.

If you use DateDiff to give you weeks, rather than days, and divide by
52,
you'll probably be better off.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message

I want to calculate someones age calculating the difference between
"DOB"
field and today Date() (So its always up to date) I want to display the
result in Years and weeks as accurately as possible, (it influences
prices
charged). I want to use an expression in a calculated field in a form,
I
cant write VBA (yet!)
The task seems to be converting tenths of a year into weeks. e.g.
=datediff("d",[DOB],Date())/365.25 produces (for example) 63.81. Whats
that
in weeks? and Is it accurate?
I'm using Access 2000 (but will soon be switching to 2003)