Software

Ask something
Discussion Locked

How do you calculate ages in Access 2000

0 helpful
5 replies

by laurie j

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a Date of Birth field in a form named "DOB" and would like to have a Text Box next to it that calculates the age using the =Date(), Today or =Now() function. Is there a clever calculation that takes into account leap years adn also returns years and months instead of e.g. 82.89?

The calculation I have at present entered in the properties of the text Box is:

"=([now]-[dob])/365".

This returns a nearly answer of 83.89 for a DOB of 21/5/1919. I would like 83 years and 10 months (ignoring left over weeks and days if there are any.)

Is this possible in Access 2000?

5 replies

by karpi

Hello,

interesting question!
You may try:

=format(datediff("d",[DOB],now),"yy \Y\e\a\r\s mm \M\o\n\t\h\s")

HTH
Karpi

by laurie j

Only problem is if the person is over 100 years old...... result becomes 01 years !!!

by charles byrne

Date Calculations are funny. To do the field calculation you need something like this:

=DateDiff("m",[DOB],Now())\12 & " Years " & ((DateDiff("m",[DOB],Now())/12)-(DateDiff("m",[DOB],Now())\12))*12 & " Months"

It's easier to have a custom function in VBA return the answer you want Insert a new module and add the following:

Function YearMonthAge(strDOB as string) as string
Dim strYears as String
Dim strMonths as String

strYears =(datediff("m",cdate(strDOB),now)\12)
strMonths = ((datediff("m",cdate(strDOB),now)/12)-(datediff("m",cdate(strDOB),now)\12))*12
YearMonthAge = strYears & " years and " & strMonths & " months"
End Function

In your field enter =YearMonthAge([DOB])

Hope this helps
Charles

This discussion is closed to new replies.