Solved

How do I calculate age in years?

Posted on 2011-03-21
18
604 Views
Last Modified: 2012-05-11
I feel really stupid asking this but cannot get the formatting or expression correct.
I  have today's date, DATE(), and I have the horses' date of birth, datHorseDateOfBirth, so I use the formula = Date()-datHorseDateOfBirth but I get big numbers.  I just want integers, rounded up.
0
Comment
Question by:bobbat
  • 3
  • 3
  • 3
  • +5
18 Comments
 
LVL 5

Assisted Solution

by:KGNickl
KGNickl earned 100 total points
ID: 35181045
(Date()-([datHorseDateOfBirth])/365.25
0
 
LVL 5

Assisted Solution

by:KGNickl
KGNickl earned 100 total points
ID: 35181065
Also, make sure to formate the field to show the number of decimals you want (I would choose two).
 

0
 
LVL 13

Expert Comment

by:Rick
ID: 35181073
SELECT
   Int(DateDiff("m",[datHorseDateOfBirth],Date()))+(Date()<DateSerial(Year(Date()),Month(Date()),Day([datHorseDateOfBirth]))) AS _Months
  , Int([_Months]/12) AS Age_Years
  , [_Months] Mod 12 AS Age_Months
FROM
   Table1;
0
 
LVL 13

Expert Comment

by:Rick
ID: 35181082
You could just use Round() on Age_Years.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 35181124
Rick is probably closest to the quick and easy (and most accurate).  Try:

DateDiff("yyyy", [DOB], Date()) + (Format([DOB], "mmyy") > Format(Date(), "mmyy"))
0
 
LVL 57
ID: 35181145
Use the attached function.  Paste into a module and then call with DOB and a date "as of".

JimD.
Function GetAge1(varBD As Variant, Optional varAsOfDate As Variant = Null) As Integer

        Dim intAge As Integer
        
10      If Nz(varBD, "") = "" Then varBD = Now()
20      If Nz(varAsOfDate, "") = "" Then varAsOfDate = Now()

30      GetAge1 = DateDiff("yyyy", varBD, varAsOfDate) + (Format(varAsOfDate, "mmdd") < Format(varBD, "mmdd"))

End Function

Open in new window

0
 
LVL 10

Expert Comment

by:khan_webguru
ID: 35181158
If we know Birth date of HORSES, you can calculate age using function DateDiff("yyyy",DateOfBirth,Date()). But this simple formula will not always give us correct results. If Birth day did not passed yet in the current year, we will receive Age bigger by 1. In order to overcome this trouble, we need to improve the formula. Can do this by substructing 1 year from the result if Birth day did not take place yet. Current year Birth date can be constructed in this manner:

DateSerial(Year( Date()), Month(DateOfBirth), Day(DateOfBirth))

Expression:

Date() < DateSerial(Year( Date()),Month(DateOfBirth), Day(DateOfBirth)))

will return (-1) if Current Date is smaller than the Birth Date of this year.

I applied query on EMPLOYEE table you can apply this on your desire table ......

 
SELECT FirstName, LastName, DateOfBirth, 
DateDiff ("yyyy",DateOfBirth, Date())+(Date() < DateSerial(Year( Date()), Month(DateOfBirth), Day(DateOfBirth))) AS Age,
Date() < DateSerial(Year( Date()), Month(DateOfBirth), Day(DateOfBirth))) AS AddYear 
FROM Employees;

Open in new window


Hope this will help you to solve the problem.

Regards,

Asif Ahmed Khan
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 35181190
Jim's function has the added benefit of accepting VARIANT (read NULL) values, which I have found to be a significant problem with databases containing DOB fields.  However, if I were going to go the route of a function, I would probably change the functions data type to Variant, and return either a NULL or a text string "Unknown" if either of the dates passed is NULL.
0
 

Author Comment

by:bobbat
ID: 35182612
KGNickl: I wrote: Age: Format((Date()-([datDateOfBirth]))/365.25,0)

Rick: In the field box of the QBE I typed Age: SELECT   Int(DateDiff("m",[datDateOfBirth],Date()))+(Date()<DateSerial(Year(Date()),Month(Date()),Day([datDateOfBirth]))) AS _Months   , Int([_Months]/12) AS Age_Years   , [_Months] Mod 12 AS Age_Months FROM tblHorses
and got a "syntax error in subquery" message

fyed: I typed Age: SELECT  IAge: DateDiff("yyyy", [datDateOfBirth], Date()) + (Format([datDateOfBirth], "mmyy") > Format(Date(), "mmyy"))  , Int([_Months]/12) AS Age_Years  , [_Months] Mod 12 AS Age_Months FROM tblHorses
and got the age in months.  Could you rewrite the expression so it gives me the age, for example, as "1 yr 3 mos"?

Kahn: I typed: AGE: SELECT [datDateOfBirth],
DateDiff ("yyyy",[datDateOfBirth], Date())+(Date() < DateSerial(Year( Date()), Month([datDateOfBirth]), Day([datDateOfBirth]))) AS Age,
Date() < DateSerial(Year( Date()), Month([datDateOfBirth]), Day([datDateOfBirth]))) AS AddYear
FROM tblHorses
but the QBE would not accept it and reverted to my original Date()-datDateOfBirth

I prefer not to use modules as I am working with the report's RecordSource as a query.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 57
ID: 35182640
<<I prefer not to use modules as I am working with the report's RecordSource as a query. >>

 Queries can call user defined functions (UDF).  Only trick is that you must reference a field so that it gets called for each row.

<<  Could you rewrite the expression so it gives me the age, for example, as "1 yr 3 mos"?>>

  Your probably going to end up with a UDF.  The amount of logic that you'll need to return the months is not something your going to be able to easily do in a SQL statement.

JimD.

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35182856
> Rick is probably closest to the quick and easy (and most accurate).  

No, at least not the most accurate - and if not, what is it worth?

First:
> How do I calculate age in years? .. I just want integers, rounded up.
Next:
> Could you rewrite the expression so it gives me the age,
> for example, as "1 yr 3 mos"?

You have to make up your mind. Should it be age in years and months or just years? And should it be "rounded up" - which doesn't return an age but a period as an age always is rounded down.

/gustav
0
 
LVL 13

Expert Comment

by:Rick
ID: 35184786
>   Rick: In the field box of the QBE I typed Age: SELECT   Int(DateDiff("m",[datDateOfBirth],Date()))+
>   (Date()<DateSerial(Year(Date()),Month(Date()),Day([datDateOfBirth]))) AS _Months   ,
>   Int([_Months]/12) AS Age_Years   , [_Months] Mod 12 AS Age_Months FROM tblHorses
>   and got a "syntax error in subquery" message

That was just to give you an idea.
You need to divide _Months by 12 and apply the Rounding. Not use what I typed.
0
 
LVL 10

Assisted Solution

by:t_hungate
t_hungate earned 50 total points
ID: 35194342
MS has a very nice sample DB that will show you exactly how to accomplish this.  It include the creation of a module that contains two functions Age() and AgeMonths().  Allowing to you show an age in two different formats.  I suggest you take a look at it and see if it helps you gather an understanding of what is bing done and how. You can download the Access 2007 version here.

http://office.microsoft.com/en-us/templates/results.aspx?qu=calculating+age&origin=CT010117248#ai:TC010288085|

I have attached the functions that are in the module if you just want to use them from here.  Just note, I did not write them, they are directly from the MS sample DB.

Hope it helps.

~:TLH:~
Option Compare Database
Option Explicit

'*************************************************************
' FUNCTION NAME: Age()
'
' PURPOSE:
'    Calculates age in years from a specified date to today's date.
'
' INPUT PARAMETERS:
'    varBirthDate:  a birth date.
'
' RETURN
'    Age in years.
'
'*************************************************************
Function Age(varBirthDate As Variant) As Integer
   Dim varAge As Variant


   If IsNull(varBirthDate) Then Age = 0: Exit Function

   varAge = DateDiff("yyyy", varBirthDate, Now)
   If Date < DateSerial(Year(Now), Month(varBirthDate), _
                        Day(varBirthDate)) Then
      varAge = varAge - 1
   End If
   Age = CInt(varAge)
End Function

'*************************************************************
' FUNCTION NAME: AgeMonths()
'
' PURPOSE:
'  Compliments the Age() function by calculating the number of months
'  that have expired since the last month supplied by the specified date.
'  If the specified date is a birthday, the function returns the number of
'    months since the last birthday.
'
' INPUT PARAMETERS:
'    varBirthDate:  a birth date.
'
' RETURN
'    Months since the last birthday.
'*************************************************************
Function AgeMonths(ByVal varBirthDate As Variant) As Integer

If IsNull(varBirthDate) Then AgeMonths = 0: Exit Function

   Dim tAge As Double
   tAge = (DateDiff("m", varBirthDate, Now))
   If (DatePart("d", varBirthDate) > DatePart("d", Now)) Then
      tAge = tAge - 1
   End If
   If tAge < 0 Then
      tAge = tAge + 1
   End If

   AgeMonths = CInt(tAge Mod 12)

End Function

Open in new window

0
 

Author Comment

by:bobbat
ID: 35195375
Thanks to all for the info.

I will stick to the age in years rounded up to the nearest integer because that's how horses are "aged".  In other words, a horse (especially in the Thoroughbred world, and we rescue a lot of them here in the Ocala, FL area) is considered to be one year old if it is born on New Year’s Day.
0
 
LVL 10

Expert Comment

by:khan_webguru
ID: 35196229
Hello Bro!

Thats a very good work and best of luck for your future and God bless you in every field of life.

Regards,

Asif Ahmed Khan
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35197097
> MS has a very nice sample DB that will show you exactly
> how to accomplish this.

No, these functions are just examples showing how not to do it as they don't take leap years into account.

> I will stick to the age in years rounded up to the nearest integer ..

This can be done this way:
Public Function AgeHorse( _
  ByVal datDateOfBirth As Date) _
  As Integer

' Returns the difference in years from datDateOfBirth to current date
' rounded up.
'
' Calculates correctly for:
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of years to dates of Feb. 29.
' when the resulting year is a common year.
' After an idea of Markus G. Fischer.
'
' 2011-03-23. Cactus Data ApS, CPH.

  Dim datToday  As Date
  Dim intAge    As Integer
  Dim intYears  As Integer
    
  datToday = #2/29/2008# 'Date
  ' Find difference in calendar years.
  intYears = DateDiff("yyyy", datDateOfBirth, datToday)
  If intYears >= 0 Then
    ' Increase by 1 if current date is equal to or later than birthday of the
    ' current year using DateDiff to ignore a time portion of datDateOfBirth.
    intAge = intYears + Abs(DateDiff("d", datToday, DateAdd("yyyy", intYears, datDateOfBirth)) <= 0)
  End If
  
  AgeHorse = intAge
  
End Function

Open in new window


> a horse .. is considered to be one year old if it is born on New Year’s Day.

This is again confusing. So how old a horse born on, say, Feb. 1th? Zero years?
And when will these two horses reach an age of 0(?), 1, and 2?

/gustav
0
 

Author Comment

by:bobbat
ID: 35199941
Gustav, I know it's confusing but I have to live within the rules if I want to get a grant from Thoroughbred-related charities.  Go to http://www.beautysequinerescue.org/tonka's_task_force.htm if you want to see what we deal with.  Thus, I don't make the rules, the Jockey Club does.
Per Jockey Club rules (http://www.jockeyclub.com/mediaCenter.asp?story=197): "The rule changes appear in Section V of the Rules. Rule 1(C) states: For the purpose of determining Age of the Thoroughbred, the date of birth of a Thoroughbred is deemed to be January 1 of the year of foaling."

For the purposes of my grant application, a horse born (foaled) anytime in 2011 is considered one year old, foaled in 2010 would be two years old, a horse foaled in 2009 is 3 years old, etc.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 350 total points
ID: 35200788
OK, that's clear and simple. Then, in your query:

AgeHorse: DateDiff("yyyy",[datDateOfBirth],Date())+1

/gustav
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now