linking the date of birth and age in access

[kerij]

I just wanted to know in access how you can link the date of brith and age so that when you enter their birthday it automatically updates their age?

[missinglinq]

To get Access to fill in the current age when you enter the Date of Birth, assuming the birth date box is called DOB and the current age box is called CurrentAge:

Private Sub DOB_AfterUpdate()
  Me.CurrentAge = DateDiff("yyyy", Me.DOB, Date) - IIf(Format$(Date, "mmdd") < Format$(Me.DOB, "mmdd"), 1, 0)
End Sub

If you also want the current age to be updated as time goes by:

Private Sub Form_Current()
  Me.CurrentAge = DateDiff("yyyy", Me.DOB, Date) - IIf(Format$(Date, "mmdd") < Format$(Me.DOB, "mmdd"), 1, 0)
End Sub
 

Welcome to TheScripts!

Linq ;0)>

[kerij]

To get Access to fill in the current age when you enter the Date of Birth, assuming the birth date box is called DOB and the current age box is called CurrentAge:

Private Sub DOB_AfterUpdate()
  Me.CurrentAge = DateDiff("yyyy", Me.DOB, Date) - IIf(Format$(Date, "mmdd") < Format$(Me.DOB, "mmdd"), 1, 0)
End Sub

If you also want the current age to be updated as time goes by:

Private Sub Form_Current()
  Me.CurrentAge = DateDiff("yyyy", Me.DOB, Date) - IIf(Format$(Date, "mmdd") < Format$(Me.DOB, "mmdd"), 1, 0)
End Sub
 

Welcome to TheScripts!

Linq ;0)>

Thanks, but where do i put the code...i have already made a table, query and form...(i do want the current age to be updated as time goes by)

Thanks,
Keri

[Jim Doherty]

Thanks, but where do i put the code...i have already made a table, query and form...(i do want the current age to be updated as time goes by)

Thanks,
Keri

Hi Keri,

As an extra snippet

You may have a reason for storing date of birth and a statically stored age as separate fields in your database table for some specific reason of course, but just in case you were 'not' aware if it is not actually necessary for you to do that, you can calculate the age based on the date of birth of the person concerned, compared to now. If this is relevant to your circumstances then paste the following into a new MODULE below the line that says 'Option Compare Database' and then save the module as bas_MyModule. You will then be able to use this function anywhere in your database to calculate the number of years since a given date and now.

 
Public Function Age(Bdate, DateToday) As Integer
On Error Resume Next
If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
    Age = Year(DateToday) - Year(Bdate) - 1
Else
Age = Year(DateToday) - Year(Bdate)
End If
    Resume Exit_Age
End Function
 

You can then reference this function in your query to calculate the age of the person concerned. The function will look at the date of birth of the person as it is 'sitting' in the queried row and reproduce a column with the age 'calculated' thus updating automatically as time goes by which is what I believe you 'might' be meaning here. If not then obviously disregard me.

Type this into the field section of the query grid. you might want to place it next to your date of birth field for easy viewing. Obviously change DateOfBirth to whatever you have called your date of birth field.

Age: Age([DateOfBirth],Now())

Regards

Jim :)