I would like a field on my form that shows a persons age.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like a field in my Access database that shows a persons age. I have
two fields now that are; Date of Birth and current date. I cannot figure out
how to create this field that takes these two fields to show the persons
current age.
Help?

Thanks
Henry
 
In a module that already exists or create a new module and add the function
below. In a field on a form you can then set the control source to
=Get_Age(DateofBirth)
DateofBirth should be the name of your DOB field.

Function Get_Age(dtDOB As Date) As Integer
Dim DOBYear, DOBMon, DOBDay, TodYear, TodMon, TodDay, Age1, Age As Integer

If IsNull(dtDOB) Or IsDate(dtDOB) = False Then
Age = -1
Else
DOBYear = Year(dtDOB)
DOBMon = Month(dtDOB)
DOBDay = Day(dtDOB)
TodYear = Year(Date)
TodMon = Month(Date)
TodDay = Day(Date)

Age1 = TodYear - DOBYear
Age = IIf(DOBMon < TodMon, Age1, IIf(DOBMon > TodMon, Age1 - 1,
IIf(DOBDay <= TodDay, Age1, Age1 - 1)))
End If
Get_Age = Age
End Function
 
Tha won't work. I am only 35 until my birthday this year. Once that
passes, I will be 36.

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


--
Rick B



smk23 said:
DateDiff("yyyy",[DateOfBirth], Now)
--
sam


Henry said:
I would like a field in my Access database that shows a persons age. I have
two fields now that are; Date of Birth and current date. I cannot figure out
how to create this field that takes these two fields to show the persons
current age.
Help?

Thanks
Henry
Click to expand...
Click to expand...
 
Or just put the following in an unbound text box or in a new field in your
query.

DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))
 
smk23 said:
DateDiff("yyyy",[DateOfBirth], Now)
Click to expand...

Sorry but that will be incorrect a large percentage of the time. For an
extreme example if someone was born on Dec 31 and you ran that expression on
Jan 1 it would indicate that they were already one year old.

You need to account for whether their birthday has already occurred in the
current year. See code at link below for example...

http://www.mvps.org/access/datetime/date0001.htm
 
This does not work if you have not had your birthday this year yet.

smk23 said:
DateDiff("yyyy",[DateOfBirth], Now)
--
sam


Henry said:
I would like a field in my Access database that shows a persons age. I have
two fields now that are; Date of Birth and current date. I cannot figure out
how to create this field that takes these two fields to show the persons
current age.
Help?

Thanks
Henry
Click to expand...
Click to expand...
 
Hey, great guys. I will mark it down. Have you checked out modulesdaovba yet?
Really fascinating questions there!
--
sam


Rick Brandt said:
smk23 said:
DateDiff("yyyy",[DateOfBirth], Now)
Click to expand...

Sorry but that will be incorrect a large percentage of the time. For an
extreme example if someone was born on Dec 31 and you ran that expression on
Jan 1 it would indicate that they were already one year old.

You need to account for whether their birthday has already occurred in the
current year. See code at link below for example...

http://www.mvps.org/access/datetime/date0001.htm
Click to expand...
 
Thanks but this did not work for me..I must be doing something wrong on my end.

I have three fields on my Access form for this.
1) Current date field
2) Bdate field
3) Age field.

Using the current date field (todays date) and the Bdate field, I should
somehow be able to have a calculation in the age field that shows the child's
age.
I have done this on another database I created but I can't get this one to
work.
On the other database I created where this works my fields are ;
1) Visit Date field
2) DOB field
3) Age field

Henry
 
Back
Top