Hello!
I am currently building a form in Access which includes a text field "Age" which, based on the date of birth, calculates the correct age. Unfortunately the DateDiff function does not work 100% of the time, which is
why I cannot rely on using the Expression Builder via theControl Source for the "Age" text field (wish Access had Excel's Yearfrac function...)
Anyway, so I wrote the code that accurately calculates the age:
Code:
Private Sub txtDayofBirth_Click()
Dim Birthdate As Date
Dim Age As Byte
Birthdate = [Day of Birth]
Age = DateDiff("yyyy", Birthdate, Date)
If Month(Birthdate) >= Month(Date) Then
If Month(Birthdate) > Month(Date) Then Age = Age - 1
If Month(Birthdate) = Month(Date) Then
If Day(Birthdate) > Day(Date) Then Age = Age - 1
End If
End If
Me.txtAge.Value = Age
End Sub
The issue I encounter is that, when I trigger this event, the age of the currently selected person is assigned to all other employees in my list. That is probably due to the code I highlighted red.
Is there any way I can I can solve this?
Edit:
Here is a picture of a sample table illustrating the issue: