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().
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...
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:
Code:
' 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.
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.
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...
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.
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...
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.
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.
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
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
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!
