Automatic age calculation

[Stephenoja]

Hello guys,
Thanks for all the help with the last question. I have another one. Does anyone know how to let access automatically calculate age and display in field when the person's date of birth is entered? Is that even possible? Any help would be greatly appreciated. Thanks.

[willakawill]

Hi. this is what microsoft has to say on this subject

[Stephenoja]

Hi. this is what microsoft has to say on this subject

Thank you Willa, I checked the site but what I actually want to do is to calculate the persons's age based on the computer system's date. So I will enter the person's birthday and access should use the system's date to calculate and display the person's age in years. Am not sure if this is possible but am still reading through some materials. Thanks again.

[willakawill]

You just need to put datediff on your form to calculate the age. The function, Now(), returns the system date and time.

DateDiff("y", #1-Feb-1995#, Now())

[NDayave]

I use this slightly longer code, but it returns the age as "15 Years 4 Months" for example.

Option Compare Database
Option Explicit
 
Public Function fnCalculateAge(strDateOfBirth As String)
    Dim intYears As Integer
    Dim intMonths As Integer
    intYears = Year(Now) - Year(strDateOfBirth)
    If Month(Now) < Month(strDateOfBirth) Or (Month(strDateOfBirth) = Month(Now) And Day(Now) < Day(strDateOfBirth)) Then
        intYears = intYears - 1
    End If
    intMonths = Month(Now) - Month(strDateOfBirth)
    If Day(Now) < Day(strDateOfBirth) Then
        intMonths = intMonths - 1
    End If
    If intMonths < 0 Then
        intMonths = intMonths + 12
    End If
    fnCalculateAge = intYears & " Years " & intMonths & " Months"
End Function

Use as a module, and enter "=fnCalculateAge([Name of Text box with Date of Birth in])" in the text box on the form you want to display the age.

Obviously you need the Date of Birth text box on the form, but it can be invisible.

This may not be what you were looking for, but i thought id give you another option on the format of the Age calculated.

NDayave

[NeoPa]

Set the Control Source of the TextBox you want it displayed in to :

=Year(Date)-Year(Me!DateOfBirth)-IIf(Format(Date,"mmdd")<Format(Me!DateOfBirth,"mmdd"),1,0)

DateDiff("yyyy",,) returns the nearest whole number of years.

[NeoPa]

Different versions can be supplied to handle months in the age as well if required.

[NDayave]

Different versions can be supplied to handle months in the age as well if required.

How would you display it as Years and Months using the DateDiff?

Im guessing it would be shorter than my code, which is always a bonus

NDayave

[Rabbit]

(DateDiff("m", DOB, Date) \ 12) & " Years " & _
   (DateDiff("m", DOB, Date) Mod 12) & " Months"

[NeoPa]

That will work for the nearest value, which is probably more acceptable when measuring in months than in simple years, rather than whole months.
DateDiff() will always round to the nearest, rather than work in whole integer values.

[Stephenoja]

thank you so much guys. i really appreciate the help.

[willakawill]

thank you so much guys. i really appreciate the help.

You are very welcome :)

[user330]

New to VBA code and this worked PERFECTLY!
Thank you!!

__________________________________________________ _________

Re: Automatic age calculation
--------------------------------------------------------------------------------

I use this slightly longer code, but it returns the age as "15 Years 4 Months" for example.

Option Compare Database
Option Explicit
 
Public Function fnCalculateAge(strDateOfBirth As String)
    Dim intYears As Integer
    Dim intMonths As Integer
    intYears = Year(Now) - Year(strDateOfBirth)
    If Month(Now) < Month(strDateOfBirth) Or (Month(strDateOfBirth) = Month(Now) And Day(Now) < Day(strDateOfBirth)) Then
        intYears = intYears - 1
    End If
    intMonths = Month(Now) - Month(strDateOfBirth)
    If Day(Now) < Day(strDateOfBirth) Then
        intMonths = intMonths - 1
    End If
    If intMonths < 0 Then
        intMonths = intMonths + 12
    End If
    fnCalculateAge = intYears & " Years " & intMonths & " Months"
End Function

Use as a module, and enter "=fnCalculateAge([Name of Text box with Date of Birth in])" in the text box on the form you want to display the age.

Obviously you need the Date of Birth text box on the form, but it can be invisible.

This may not be what you were looking for, but i thought id give you another option on the format of the Age calculated.

NDayave

[NeoPa]

I assume from the signature that this is actually NDayave. Let me know if you're having trouble with your account.

(also try to remember to use the [ CODE ] tags in your posts please).