SQL advanced -> 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