Calculating Age in Years and Months from Date of Birth

[paulS30berks]

I have a Date of Birth field in format DD/MM/YYYY and wish to set up 2 new fields one that calculates Age in Years and another that calculates Age in Months.

Thanks

 

[ColinEssex]

A search of the forum would have shown there is an example in the code repository

Col

 

[paulS30berks]

This is true, however I do not wish to use VBA for a simple Select Query and having read through the other example this just confused me

 

[ColinEssex]

If you wish to calculate the age in years and months from the DOB, you will need to use code because you need to create new calculated fields using the DOB as the source.

Col

 

[paulS30berks]

Ok stupid question time I have an existing Select query and wish to include these calculated fields. How can I install this code within my select query?

Thanks

 

[ColinEssex]

Copy and paste the code as a Function(), make sure your field names are correct in the code and call the Function() from the query grid as a calculated field.

Col

 

[paulS30berks]

Sorry never done this before...I have my code, and now need to add a function? How do I do this? Once function created, I recall this via the Build option in my select query is this correct?

 

[Brianwarnock]

In query grid at field row Age_in_years: functionname(fld1,fld2)
assuming function takes in 2 fields.

Brian

 

[paulS30berks]

Sorry i meant how and where to add the function? So that i can recall from The Select Query.

 

[Brianwarnock]

Save it as a public function in a module ensuring that the module name is different from all functions thatit contains.

Brian

 

[raskew]

Hi -

I'm not sure whether you want a result in terms of Years & Months or one that gives you two results:


1) Date of Birth expressed in years
2) Date of Birth expressed in months.

In the first possibility, someone born 08-Dec-48 would be--as of 19-Jan-06--57 years, 1 month old

In the second possibility, the same person would be:
1) 57 years old
2) 685 months old

Whatever you're looking to achieve, the following SELECT query, based on Northwind's Employees table, addresses all possibilities. Change table and field names to correspond with your application.

HTH - Bob

SELECT
    LastName
  , FirstName
  , BirthDate
  , Int(DateDiff("m",[BirthDate],Date()))+(Date()<DateSerial(Year(Date()),Month(Date()),Day([BirthDate]))) AS MyMonths
  , Int([MyMonths]/12) AS AgeYears
  , [MyMonths] Mod 12 AS AgeMonths
FROM
   Employees;

 

[paulS30berks]

Its seems as though no MS expression assumes that an age calculation is based on a 12 month cycle.

The result I am actually looking for is for example:

Date of Birth = 01/12/2004

Age In Years = 1
Age In Months = 1

Age In Months is calculated against todays date and based on assumption there are 12 months to a year, so as 01/11/2006 results would show:

Age In Years = 1
Age In Months = 11

The only thing I am unsure of is once the date reaches 01/12/2006 - does the Age In Months calculation assume 12 months or revert back to 0 to identify a further year.

Thanks

Paul

 

[rahulgty]

I think u r looking for that

rahulgty

 

[statsman]

Or you could use the DateDiff function:

For years
=DateDiff("yyyy",[dob],[secondfieldname])

For months
=DateDiff("mmmm",[dob],[secondfieldname])

 

[raskew]

Paul –

It doesn’t sound as if you’re implementing the suggestions that have been provided.

The one I previously provided will return age in months, years & years/months. Try it verbatim (copy/paste) in a new query against Northwind’s Employees table.

You’re correct that there is no built-in function to return the year/month difference. If needed, and it’s not, you’ll have to ‘roll-your-own’. In the referenced example, there are no functions used other than DateDiff(), Int() and the Mod operator and no code is required.

Check the Help-File for Int() and the Mod operator.

If you use DateDiff()* to return the number of full months, e.g. 23, then

Int(23/12) = 1 (year)
23 mod 12 = 11 (months)

* It may seem that the DateDiff() function should return Age based on StartDate and a user-specified EndDate. However, there's a problem with that solution since the DateDiff("yyyy"....) merely subtracts one year from another without regard to months and days. Example:

? DateDiff(“yyyy”, #12/31/05#, #1/1/06#) returns 1 – obviously incorrect since only one day has past

Likewise: ? DateDiff(“m”, #12/31/05#, #1/1/06#) returns 1, which is equally incorrect since only one day has past.

To accurately return age in years, we must know whether the month/day portion of the EndDate is >= than that of the StartDate.

Likewise, to accurately return age in months, we must know whether the day portion of the EndDate is >= that that of the StartDate.

To resolve, we can add a boolean statement that examines month/days (in the case of years) or days (in the case of months) of the two dates and returns -1 (false) or 0 (true) to indicate if the month/day (years) or days (months) of the EndDate meets or exceeds that of the StartDate.

For computation in years:
Age = DateDiff("yyyy", StartDate, EndDate) + (EndDate < DateSerial(Year(EndDate), Month(StartDate), Day(StartDate)))

For computation in months:
Age = DateDiff(“m”, StartDate, EndDate) + (EndDate < DateSerial(Year(EndDate), Month(EndDate), Day([StartDate])))

While there’s no real need for a user-defined function to be called from a SELECT query, you could have one if you wanted and needed to add an additional layer of complexity to the process:

Function fAgeYM(StartDate As Date, EndDate As Date) As String
'Purpose:   Returns the difference between StartDate and Date in full years and months
'Coded by:  raskew
'To call:
' ? fAgeYM(#1/21/04#, #1/19/06#)
'Returns:
' 1 years 11 months

Dim intHold As Integer


   intHold = Int(DateDiff("m", StartDate, EndDate)) + _
             (EndDate < DateSerial(Year(EndDate), Month(EndDate), Day(StartDate)))
   
   fAgeYM = Int(intHold / 12) & " years " & intHold Mod 12 & " months "

End Function

Bob

 

[oldtimer]

I might be able to help with that

Pretty straight forward, do it in a text box.
This will give you the age of a person, make sure you alter the properties to remove the decimal places

=(Now()-[DOB])/365

Good luck

 

[Brianwarnock]

Sorry Oldtimer far too simplistic, read Post#15 by Raskew.

Don't believe me, a simple example will suffice

DOB 1st march 2003

age 29th Feb 2004 is 365 days but 0 years.

Brian

 

[ColinEssex]

In addition to what Brian said, OldTimers calculation doesn't calculate the months.

Col

 

[LOgle0917]

You probably already have the answer to this but this is a simple way to do this:

In my database I have a field that gives me the date of birth I created a text field that calculates the age in years and months by putting this in the control source field : =CalcAge([DOB]) this returns the age in years and months.

Thanks!

 

[IpeXeuS]

Hello guys,

This was exactly what I was looking for, but tell me genious mates is there way to add year and month values from text fields to that difference between two dates?