Automatic display of age when a date of birth is inputted 1

[ngoz1]

Hi every one,

I have designed a database which my organisation is using to store information on clients with learning difficulties.

At the moment i input the date of birth(DOB) and age in the respective fields myself. But i would like the age of the client to be automatically calculated and displayed in the 'age' field once i enter the Date of birth in the DOB field

Does any one know how to do this ?

Thanks
Ngozi

 

[Norris68]

There have been a few threads on here regarding calculation ages from DOBs. However, to repeat them, use DateDiff("yyyy",DOB,Date()). So, in your DOB field AfterUpdate event set the age to the result of the DateDiff function.

You probably won't want to store it though, as you will have to recalculate it next year. You are probably best to calculate it 'on-the-fly', calling it within the Form Current and DOB AfterUpdate events & displaying the result in a label control. e.g.

Private Sub DisplayAge()
If IsDate(DOB) Then
Me.lblAge.Caption = DateDiff("yyyy",DOB,Date())
Else
Me.lblAge.Caption = ""
End If
End Sub

Private Sub Form_Current()
DisplayAge
End Sub

Private Sub DOB_AfterUpdate()
DisplayAge
End Sub

 

[FancyPrairie]

I don't believe the DisplayAge sub is correct. It doesn't take into account that my birthday may not have occured yet this year. For example, 3/27/72 would produce an age of 31 as would 12/27/72.

Here's one that should work (unless the person is less then 1)

Set the ControlSource of the Age control to:

ControlSource... =CalcAge([YourDateField])

Function CalcAge(dteBirthdate As Date) As Long

Dim lngAge As Long

' Make sure passed-in value is a date.
If Not IsDate(dteBirthdate) Then
dteBirthdate = Date
End If

' Make sure birthdate is not in the future.
' If it is, use today's date.
If dteBirthdate > Date Then
dteBirthdate = Date
End If

' Calculate the difference in years between today and birthdate.
lngAge = DateDiff("yyyy", dteBirthdate, Date)
' If birthdate has not occurred this year, subtract 1 from age.
If DateSerial(Year(Date), Month(dteBirthdate), Day(dteBirthdate)) > Date Then
lngAge = lngAge - 1
End If
CalcAge = lngAge
End Function

 

[BobJacksonNCI]

Hi Ngozi

I don't like to type that much...

int(((date() - DOB) +1) /365.2425)

Please view Thread181-473997 for helpful hints regarding this site.