Microsoft Access Query formula Age future date

Question

How to calculate age from date of birth Like 11 year old 11 how date ' 16 year old How Date

for any given date, plus calculate when you will be a certain age, and how many days until your next birthday.

DOB            YEAR OLD YEAR OLD DATE

2/02/2008           16            2/02/2024

6/05/2006           16            6/05/2022

31/09/2019.          11            31/09/2030

25/12/2016           16            25/12/2032

7/04/2011           11            7/04/2022

Answer 1

In a query based on the table:

YEAR OLD DATE: DateAdd("yyyy",[YEAR OLD],[DOB])

For the number of days until the next birthday, I'd use a custom VBA function:

Function DaysUntil(DOB As Date) As Long

    Dim Age As Long

    Dim Birthday As Date

    Age = DateDiff("yyyy", DOB, Date)

    Birthday = DateAdd("yyyy", Age, DOB)

    If Birthday < Date Then

       Birthday = DateAdd("yyyy", 1, Birthday)

    End If

    DaysUntil = Birthday - Date

End Function

In a query:

DAYS_UNTIL_BIRTHDAY: DaysUntil([DOB])

Answer 2

You can return the date of a future birthday by calling the DateAdd function, e.g. in the immediate window:

DoB = #2008-02-02#

? DateAdd("yyyy",16,DoB)

02/02/2024

A person's next birthday can be returned by means of the DateSerial function, adjusting the year argument on the basis of the relative position of the person's birthday in the calendar year:

? DateSerial(Year(Date()) + IIf(Format(Date(), "mmdd") > Format(Month(DoB), "00") & Format(Day(DoB), "00"), 1, 0), Month(DoB), Day(DoB))

02/02/2021

To return the number of days from the current date until the person's next birthday you therefore need to subtract the current date from the return value of the above expression:

? DateSerial(Year(Date()) + IIf(Format(Date(), "mmdd") > Format(Month(DoB), "00") & Format(Day(DoB), "00"), 1, 0), Month(DoB), Day(DoB))-Date()

 19

Answer 3

[Year Old Date]=DateAdd("yyyy",[Year Old],DOB)

DaysToBirhtday = DateDiff("d", Date(), cDate(Format(DOB,"mm/dd/") & iif(Date() < cDate(Format(DOB,"mm/dd/") & Year(Date())) , _                                              Year(Date()),Year(Date())+1)))