Query to calculate a persons age in Years & Months

[Jeepster]

Hi there,

I want to run a report that will calculate an employee’s age if I enter a date in a prompt box. My efforts so far produce the person’s age as a decimal. For instance, if an employee is 40 years and three months old (today), the query returns a figure of 40.25. How do I write a query that will show the person’s age in years and months (rounded up or down)?

Thank you

 

[Andrew Fergus]

Hi
Assuming you have a field in your table called 'DOB' (be sure to modify according to your actual field name) then the age in years up to today is calculated in a query using the following expression :

Years : Year(Date()) - Year([DOB]) - IIf(Month(Date()) < Month([DOB]), 1, IIf(Month(Date()) > Month([DOB]), 0, IIf(Day(Date())<Day([DOB]), 1, 0)))

And the months can be calculated in the same query using this expression :

Months : Month(Date()) - Month([DOB]) + IIf(Month(Date()) < Month([DOB]), 12, IIf(Month(Date()) = Month([DOB]) And Day(Date()) < Day([DOB]), 12, 0)) - IIf(Day(Date()) < Day([DOB]), 1, 0)

If you want to choose a date rather than compare the birthday to today, then use something like the following instead :

Years : Year([Enter Date])-Year([DOB]) - IIf(Month([Enter Date]) < Month([DOB]), 1, IIf(Month([Enter Date]) > Month([DOB]), 0, IIf(Day([Enter Date]) < Day([DOB]), 1, 0)))

Months : Month([Enter Date]) - Month([DOB]) + IIf(Month([Enter Date]) < Month([DOB]), 12, IIf(Month([Enter Date]) = Month([DOB]) And Day([Enter Date]) < Day([DOB]), 12, 0)) - IIf(Day([Enter Date]) < Day([DOB]), 1, 0)

The 2 fields can be joined into 1 field using the ampersand symbol with some basic text formatting, e.g.

Year([Enter Date]) - .... <Day([DOB]),1,0))) & " Years, " & Month([Enter Date]) .... Day([DOB]),1,0) & " Months".

The 4 dots signify the missing parts of the code which would need to be included. Remember to use your actual field name.

HTH, Andrew

 

[Jeepster]

Thanks..

Andrew

This is exactly what I was looking for. Your help and quick response is greatly appreciated.

Many thanks


Alan