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

[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

 

[Guest]

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

 

[Guest]

DateDiff("yyyy",[DateOfBirth], Now)

 

[Rick B]

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

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

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

 

[Rick B]

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"))

 

[Rick Brandt]

DateDiff("yyyy",[DateOfBirth], Now)

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

 

[Guest]

This does not work if you have not had your birthday this year yet.

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

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

 

[Guest]

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

DateDiff("yyyy",[DateOfBirth], Now)

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

 

[Guest]

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