Closest age

[Stew]

I need to not only calculate a person's age based on their date of birth (no problem with this) but also the age they are closest to. It's an insurance policy thing. If a person is 47 years old but within a half year of being 48 (closer to being 48) then I want that age to appear in a calculated field.

I have searched for and found many age calculating functions and formulas but none seem to cover what I am looking for. I will continue with my efforts after I post this but I thought someone might have done something similar and I could save myself some time. Any suggestions?

If I manage to create a function that works I will post it here.

 

[Matthew Snook]

Try this:

Private Sub Command2_Click() ' you don't have to use a button click...
Dim SerToday As Date
Dim SerBirthday As Date
Dim SerThisBirthday As Date
Dim SerNextBirthday As Date
Dim SerLastBirthday As Date
SerToday = Date
SerBirthday = DateSerial(Year(Me!Birthday), Month(Me!Birthday), Day(Me!Birthday))
SerThisBirthday = DateSerial(Year(Now()), Month(Me!Birthday), Day(Me!Birthday))
If SerThisBirthday < SerToday Then
'Party's over!!!
SerNextBirthday = DateSerial(Year(Now()) + 1, Month(Me!Birthday), Day(Me!Birthday))
SerLastBirthday = DateSerial(Year(Now()), Month(Me!Birthday), Day(Me!Birthday))
Else
'Party's yet to come!!!
SerNextBirthday = DateSerial(Year(Now()), Month(Me!Birthday), Day(Me!Birthday))
SerLastBirthday = DateSerial(Year(Now()) - 1, Month(Me!Birthday), Day(Me!Birthday))
End If
If Abs(SerToday - SerLastBirthday) > Abs(SerToday - SerNextBirthday) Then
Me!Age = Year(SerNextBirthday) - Year(Me!Birthday)
Else
Me!Age = Year(SerLastBirthday) - Year(Me!Birthday)
End If
End Sub

Where Me!Birthday is a date field storing the person's date of birth in the table supporting the form, and Me!Age is a textbox.

The first "IF" statement determines whether the birthday has already passed for this year, and sets the "Next" and "Last" birthdays accordingly. The next "IF" statement determines which birthday is closest and uses that date to set the age.

I tried to think of a slicker way to do it than using two "if"s, but this was stuck in my head 'til I got it done. Now I'll probably think of a better way. Tell me if it works for you...

Matt

 

[charityg]

=datediff("m",now(),[birthdate])/12

should work if you set the field format to 0 decimal places.

 

[Stew]

Thanks Matthew and charityg for your replies. While I like short pieces of code, I did some testing and Matthew's worked out to be a bit more accurate when the dates were close to a half year. just in case anyone else uses the code, I did end up throwing in some extra code in case the date of birth field was null just in case anyone else uses the code. Thanks again.