# Thread: Recording Age DateDiff with years and months

1. Registered User
Join Date
May 2002
Posts
157

## Unanswered: Recording Age DateDiff with years and months

I have the following which works quite well when only needing the age in years:
If IsNull(Me![CAgeAtTOIR]) Then Me![CAgeAtTOIR].Value = DateDiff("yyyy", [CDateOfBirth], Now()) + Int(Format(Now(), "mmdd") < Format([CDateOfBirth], "mmdd"))

I know the following works if I only want to record the age in months:
If IsNull(Me![CAgeAtTOIR]) Then Me![CAgeAtTOIR].Value = DateDiff("m", [CDateOfBirth], Now()) + Int(Format(Now(), "mmdd") < Format([CDateOfBirth], "mmdd"))

I can not work out how to get the answer in years and months, for example
a person born on 01/01/2011 (and today's date is 6/2/2011) result in 0.01
a person born on 01/01/2010 (and today's date is 6/2/2011) result in 1.01
a person born on 01/02/2010 (and today's date is 6/2/2011) result in 1.00
a person born on 01/08/2010 (and today's date is 6/2/2011) result in 0.06
a person born on 01/03/2010 (and today's date is 6/2/2011) result in 0.11

(results given in years and months)

Can this be done?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
you can probably exploit a kown quirk in MS Access / JET whic is that date/time values are stored as a number of clicks from a known dates (IIRC its first Jan 1900 or soemthign like that)

so if yous subtract the values you have the difference in number of clicks from whatever that date is
formatting the resultant value using the appropriate month and year values should give you want you want.

however this is very very kludgey, not to be used if you think your db may use data storage mechanism

the other way is to write a function which calculates the age in years and months. that is what I woudl expect is 'the' right way

eg

3. Moderator
Join Date
Mar 2009
Posts
5,442
Dear Karen,

This function should provide what you're looking for:
Code:
```Public Function DecimalAge(ByVal DateOfBirth As Date) As Variant

Dim lngMonths As Long
Dim dteFirstDay As Date

If IsNull(DateOfBirth) Then
DecimalAge = Null
Else
dteFirstDay = CDate(Month(Now) & "/1/" & Year(Now))
lngMonths = DateDiff("m", DateOfBirth, dteFirstDay)
DecimalAge = Format(lngMonths \ 12, "0") & "." & Format(lngMonths Mod 12, "00")
End If

End Function```
If put in an independent module (i.e. not the module linked to a Form or Report nor a Class module), it can be called from everywhere in your application from another VBA procedure or it can be used in a Query.

However, be aware that it accepts date in the American format (i.e. mm/dd/yyyy) which is the standard for Access. Should you decide that the date must always be passed in the (more common to us non-American people) Europeran format (i.e. dd/mm/yyyy). You should perform the necessary conversion when calling it or you could change the code accordingly.

Regards.

(P.S. I hope you're doing well).

4. Registered User
Join Date
May 2002
Posts
157
Hi there,

Thanks for the information. I tried and tried to get what you suggested to work but could not. In the end, I ended up with the following which does work.

********************************
Dim totalmonths As Integer
Dim monthsremaining As Integer
Dim years As Integer

If IsNull(Me![CAgeAtTOIR]) Then

'Ensure that the field containing the result (eg CAgeAtTOIR) is formatted as a TEXT field within the table as the concatenation of the two variables and the "." turns the result into text.
'Time in whole years
years = DateDiff("yyyy", [CDateOfBirth], Now()) + Int(Format(Now(), "mmdd") < Format([CDateOfBirth], "mmdd"))
'Time difference in whole months
totalmonths = DateDiff("m", [CDateOfBirth], Now()) + Int(Format(Now(), "mmdd") < Format([CDateOfBirth], "mmdd"))

monthsremaining = totalmonths - (years * 12)
Me.CAgeAtTOIR.Value = years & "." & monthsremaining
End If

******************

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
Here some Code I use all the Time
Code:
```Function Age(Birth_Date, End_Date)
'***************************************
' Works out the age of to the month
'
'***************************************
Dim Months
Dim Years
Dim Temp
'Check if no error
If IsNull(Birth_Date) Or Birth_Date = "" Or Not IsDate(Birth_Date) Then
Age = 0#
Else

Months = DateDiff("m", CDate(Birth_Date), End_Date)
Years = Int(Months / 12)
Temp = Years * 12
If Years = 0 Then Years = ""
Age = Years & "." & Months - Temp

End If
End Function```
passing the 2 dates make so you can use in differnace ways

want a 2 dec place

change

Age = Years & "." & Months - Temp
to
Age = Years & "." & format(Months - Temp,"00")

to use it create a module paste the code into the module window

in a query

AGE:Age([dateofbirthfeild],now())

Memberfor: age([datejoin],now())

or

AgeJoin: age([dateofbirthfeild],[datejoin])

in code

aa = Age(#1/1/1965#, Now())

what is return is Year.month

45.11 = 45 and 11 months
Last edited by myle; 02-07-11 at 05:23.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•