472,785 Members | 1,005 Online

# Automatic age calculation

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.
Feb 25 '07 #1
13 14720
willakawill
1,646 1GB
Hi. this is what microsoft has to say on this subject
Feb 25 '07 #2
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.
Feb 25 '07 #3
willakawill
1,646 1GB
You just need to put datediff on your form to calculate the age. The function, Now(), returns the system date and time.
Expand|Select|Wrap|Line Numbers
1. DateDiff("y", #1-Feb-1995#, Now())
Feb 25 '07 #4
NDayave
92
I use this slightly longer code, but it returns the age as "15 Years 4 Months" for example.

Expand|Select|Wrap|Line Numbers
1. Option Compare Database
2. Option Explicit
3.
4. Public Function fnCalculateAge(strDateOfBirth As String)
5.     Dim intYears As Integer
6.     Dim intMonths As Integer
7.     intYears = Year(Now) - Year(strDateOfBirth)
8.     If Month(Now) < Month(strDateOfBirth) Or (Month(strDateOfBirth) = Month(Now) And Day(Now) < Day(strDateOfBirth)) Then
9.         intYears = intYears - 1
10.     End If
11.     intMonths = Month(Now) - Month(strDateOfBirth)
12.     If Day(Now) < Day(strDateOfBirth) Then
13.         intMonths = intMonths - 1
14.     End If
15.     If intMonths < 0 Then
16.         intMonths = intMonths + 12
17.     End If
18.     fnCalculateAge = intYears & " Years " & intMonths & " Months"
19. 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
Feb 25 '07 #5
NeoPa
32,534 Expert Mod 16PB
Set the Control Source of the TextBox you want it displayed in to :
Expand|Select|Wrap|Line Numbers
1. =Year(Date)-Year(Me!DateOfBirth)-IIf(Format(Date,"mmdd")<Format(Me!DateOfBirth,"mmdd"),1,0)
DateDiff("yyyy",,) returns the nearest whole number of years.
Feb 26 '07 #6
NeoPa
32,534 Expert Mod 16PB
Different versions can be supplied to handle months in the age as well if required.
Feb 26 '07 #7
NDayave
92
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
Feb 27 '07 #8
Rabbit
12,516 Expert Mod 8TB
Expand|Select|Wrap|Line Numbers
1. (DateDiff("m", DOB, Date) \ 12) & " Years " & _
2.    (DateDiff("m", DOB, Date) Mod 12) & " Months"
Feb 27 '07 #9
NeoPa
32,534 Expert Mod 16PB
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.
Feb 27 '07 #10
thank you so much guys. i really appreciate the help.
Mar 4 '07 #11
willakawill
1,646 1GB
thank you so much guys. i really appreciate the help.
You are very welcome :)
Mar 4 '07 #12
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.
Expand|Select|Wrap|Line Numbers
1. Option Compare Database
2. Option Explicit
3.
4. Public Function fnCalculateAge(strDateOfBirth As String)
5.     Dim intYears As Integer
6.     Dim intMonths As Integer
7.     intYears = Year(Now) - Year(strDateOfBirth)
8.     If Month(Now) < Month(strDateOfBirth) Or (Month(strDateOfBirth) = Month(Now) And Day(Now) < Day(strDateOfBirth)) Then
9.         intYears = intYears - 1
10.     End If
11.     intMonths = Month(Now) - Month(strDateOfBirth)
12.     If Day(Now) < Day(strDateOfBirth) Then
13.         intMonths = intMonths - 1
14.     End If
15.     If intMonths < 0 Then
16.         intMonths = intMonths + 12
17.     End If
18.     fnCalculateAge = intYears & " Years " & intMonths & " Months"
19. 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
Jan 27 '08 #13
NeoPa
32,534 Expert Mod 16PB
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).
Jan 27 '08 #14