Age Calculation On Specific Date (1 Viewer)

[Eyeswideopen11]

Hi,
I am working in a database and I need to calculate a persons age on a specific date. I am having a little trouble coming up with the correct expression for this, can someone help please? I have the DOB each person and use the DateDiff function I can calculate age as of Date().

Thanks,

 

[Minty]

Have a look at this post, and similar threads below:

query calculates the age

Hello guys I have this query that calculates the age of the employee, works fine for a few seconds and then an error message pops up. please help SELECT tblmastr.ID, tblmastr.StatFig, tblmastr.Rtba, tblmastr.FullName, tblmastr.Department, fnAge([DateBirth],Date()) AS Age FROM tblmastr WHERE...

www.access-programmers.co.uk

 

[Gasman]

Has been asked many times here. A search would be beneficial.

 

[Jon]

To calculate a person's age on a specific date, you can use the DateDiff function in Microsoft Access. The DateDiff function calculates the difference between two dates, and you can use it to calculate the number of years, months, or days between a person's date of birth and a specific date.

Here is an example of how you can use the DateDiff function to calculate a person's age on a specific date:

' Calculate the number of years between the person's date of birth and the target date
Dim AgeInYears As Integer
AgeInYears = DateDiff("yyyy", [DOB], #12/31/2022#)

' Calculate the number of months between the person's date of birth and the target date
Dim AgeInMonths As Integer
AgeInMonths = DateDiff("m", [DOB], #12/31/2022#)

' Calculate the number of days between the person's date of birth and the target date
Dim AgeInDays As Integer
AgeInDays = DateDiff("d", [DOB], #12/31/2022#)

In this example, the DateDiff function is used to calculate the number of years, months, and days between a person's date of birth and December 31, 2022. The yyyy, m, and d arguments specify the units of time to use in the calculation (years, months, or days, respectively).

You can use this approach to calculate a person's age on any specific date. Simply replace the target date in the DateDiff function with the date you want to use. I hope this helps. Let me know if you have any other questions.

 

[Minty]

@Jon You'll get an overflow error if the person is over 90 years old for your days calculations...

 

[Jon]

@Jon You'll get an overflow error if the person is over 90 years old for your days calculations...

I didn't write the above. I'm testing the AI chatbot. But out of curiosity, why would you get an overflow error?

 

[Minty]

I thought as much
The Integer data type can only hold a maximum value of 32768.

32768 / 365.25 = 89.713

90 / 365.25 = 32,872.5 = overflow

 

[Eyeswideopen11]

Sorry I left out some information. I need to be able to calculate the age of a person when the book is returned in order to charge a fee for days past due. The fee structure is for anyone 18 and under on the date of book return is charged is $0.5*DaysPastDue and for anyone over 18 at the time the book is returned is charged $.10*DaysPastDue.

 

[Minty]

When is someone over 18 by your definition, the day after their 18th birthday, or the day of their 19th birthday?
The result is all determined by the exact definition...

 

[Jon]

I thought as much
The Integer data type can only hold a maximum value of 32768.

32768 / 365.25 = 89.713

90 / 365.25 = 32,872.5 = overflow

Ah, good point!

You should know, my VBA skills are rubbish nowadays. I haven't coded in VBA for over 5 years. Also, when you learn other programming languages, you get a crossover pollution where you end up mixing two languages together.

 

[Eyeswideopen11]

When is someone over 18 by your definition, the day after their 18th birthday, or the day of their 19th birthday?
The result is all determined by the exact definition...

I see that is something to consider. It would be on the day after their 18th birthday.

 

[Minty]

Ah, good point!

You should know, my VBA skills are rubbish nowadays. I haven't coded in VBA for over 5 years. Also, when you learn other programming languages, you get a crossover pollution where you end up mixing two languages together.

I suffer from that simply between SQL server and Access, so other languages are likely to be even worse.

 

[Jon]

I suffer from that simply between SQL server and Access, so other languages are likely to be even worse.

The good thing is that although syntax may differ, once you know how to program, there are always loops, conditionals, etc and so a good IDE can help catch a lot of the syntax errors.

 

[Minty]

I see that is something to consider. It would be on the day after their 18th birthday.

A simple function something like

Function fnOverEighteen(dDOB As Date, dReturnDate As Date) As Boolean
    
    Dim iYears As Long
    Dim iDays As Long
    
    iYears = DateDiff("yyyy", dDOB, dReturnDate)
    iDays = DateDiff("d", DateSerial(Year(Date), Month(dDOB), Day(dDOB)), dReturnDate)
    'Debug.Print iYears, iDays
    Select Case iYears
        Case Is > 18
            fnOverEighteen = True
        Case Is < 18
            fnOverEighteen = False
        Case Else
            If iDays <= 0 Then
                fnOverEighteen = False
            Else
                fnOverEighteen = True
            End If
    End Select

End Function

Produces the following results in the immediate window

? fnOverEighteen(#2004-12-09#,date())
False
? fnOverEighteen(#2004-12-08#,date())
True
? fnOverEighteen(#2004-12-10#,date())
False

 

[ebs17]

Function fnOverEighteen_2(dDOB As Date, dReturnDate As Date) As Boolean
    fnOverEighteen_2 = DateDiff("yyyy", dDOB, dReturnDate - 1) + _
                       (Format(dReturnDate - 1, "mmdd") < Format(dDOB, "mmdd")) >= 18
End Function

Sub test_fnOverEighteen_2()
    Dim X As Date, i As Long
    X = #12/8/2022#
    For i = 0 To 5
        Debug.Print #12/10/2004#, X + i, fnOverEighteen_2(#12/10/2004#, X + i)
    Next
End Sub

 

[isladogs]

You can find various functions for calculating age to different levels of precision in this article

Calculate Age

This article includes several functions to calculate age in years, years & months or years, months and days. It even has a function to calculate age in Years, Months, Days, Hours, Minutes And Seconds!

isladogs.co.uk