age from date of birth

[CTC - Colin]

HI

I have a form that has a date of birth field and I would like another field
in the same form to be automatically updated with the Age of the subect.

I'm sure its simple but I've tried several ways with not luck.

Thanks for any help.

Colin

 

[Niklas Östergren]

You could use this:

This code goes in a code module:
'================================
Function Age(DoB, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

If Month(DateToday) < Month(DoB) Or (Month(DateToday) = _
Month(DoB) And Day(DateToday) < Day(DoB)) Then
Age = Year(DateToday) - Year(DoB) - 1
Else
Age = Year(DateToday) - Year(DoB)
End If
End Function
'=================================

Then you use the function in your form, for instance in the Current_Event of
the form where your control is. The control you wish to display the age in.
Use the the function like this:

'=====================================
Dim intAge As Integer ' Holding calculated age for current record

' Calculating the age using function <Age>
intAge = Age(Me.txtDoB, Date)

' Setting calculated age in your control on form
Me.NameOfYourControl = intAge
'====================================

Dont´r forget to change (NameOfYourControl ) to the real name of your
control.

// Niklas

 

[Steve Schapel]

Colin,

Use an unbound textbox on the form for the Age, with its Control Source
property set to the equivalent of...

=DateDiff("yyyy",[DoB],Date())+(Format([DoB],"mmdd")>Format(Date(),"mmdd"))

If you're meaning is that your form's underlying table has an Age field,
then I would strongly advise against this. Just remove the field from
the table. Whenever you need the age throughout your application for
your purposes of form or report, it should be calculated "on the fly"
from the date of birth, using an expression similar to the above.

 

[CTC - Colin]

Worked a treat.

Thank you very much.

Colin

Colin,

Use an unbound textbox on the form for the Age, with its Control Source
property set to the equivalent of...

=DateDiff("yyyy",[DoB],Date())+(Format([DoB],"mmdd")>Format(Date(),"mmdd"))

If you're meaning is that your form's underlying table has an Age field,
then I would strongly advise against this. Just remove the field from
the table. Whenever you need the age throughout your application for
your purposes of form or report, it should be calculated "on the fly"
from the date of birth, using an expression similar to the above.

--
Steve Schapel, Microsoft Access MVP

HI

I have a form that has a date of birth field and I would like another field
in the same form to be automatically updated with the Age of the subect.

I'm sure its simple but I've tried several ways with not luck.

Thanks for any help.

Colin