How do I calculate age in years?

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;

You could just use Round() on Age_Years.

Rick is probably closest to the quick and easy (and most accurate).  Try:

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

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

Select allOpen in new window

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;

Select allOpen in new window

Hope this will help you to solve the problem.

Regards,

Asif Ahmed Khan

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.

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.

<<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.

> 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

>   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.

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.

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

> 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

Select allOpen 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

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.