Calculate correct age in MS-Access query

Calculate correct age in MS-Access query

If we know Birth date of employee, we can calculate his/her age using function DateDiff("yyyy",DateOfBirth,Date()). But this simple formula will not allways give us correct results. If Birth day did not passed yet in the current year, we will recive 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.

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;

The results will look like this:

FirstName	LastName	DateOfBirth	Age	AddYear
john	silver	08/12/58	49	-1
gret	garbo	15/01/49	59	0
le	chen	01/10/69	38	-1
daniel	defo	01/04/78	30	0
maggi	forth	25/02/52	56	0
voich	lutz	30/01/82	26	0

sqlexamples.info