Age Nearest

[Little_Man22]

Hi,

I need a field that calculates age nearest from birthdate. Currenlty I am able to calculate age from birthdate but I am unable to get age nearest (i.e. a person who is 39 years and 8 months old will show up on my report as 40).

Thanks.

 

[Stew]

I was just searching on a topic when I came across your post. I don't know if you've found a solution yet so here is a function I use to calculate nearest age. I actually got this from a friend in a forum and put in a touch of my own.

Dim SerToday As Date
Dim SerBirthday As Date
Dim SerThisBirthday As Date
Dim SerNextBirthday As Date
Dim SerLastBirthday As Date
SerToday = Date

If IsNull([BIRTHDATE]) Then
Me.txt_age_nearest.Value = ""
Else
SerBirthday = DateSerial(year(Me.BIRTHDATE), Month(Me.BIRTHDATE), Day(Me.BIRTHDATE))
SerThisBirthday = DateSerial(year(Now()), Month(Me.BIRTHDATE), Day(Me.BIRTHDATE))

If SerThisBirthday < SerToday Then
SerNextBirthday = DateSerial(year(Now()) + 1, Month(Me.BIRTHDATE), Day(Me.BIRTHDATE))
SerLastBirthday = DateSerial(year(Now()), Month(Me.BIRTHDATE), Day(Me.BIRTHDATE))
Else

SerNextBirthday = DateSerial(year(Now()), Month(Me.BIRTHDATE), Day(Me.BIRTHDATE))
SerLastBirthday = DateSerial(year(Now()) - 1, Month(Me.BIRTHDATE), Day(Me.BIRTHDATE))
End If

If Abs(SerToday - SerLastBirthday) > Abs(SerToday - SerNextBirthday) Then
Me.txt_age_nearest = year(SerNextBirthday) - year(Me.BIRTHDATE)
Else
Me.txt_age_nearest = year(SerLastBirthday) - year(Me.BIRTHDATE)
End If
End If

I hope it is of help to you.

Stew

 

[xxlu]

Hi, there is a simple way I used to calculate the nearest age. The formula for the calculate text box is as follows:

=((DateDiff("d",[DOB],Now()))-(DateDiff("d",[DOB],Now())\365)/4)\365.

You may try it.