how to write; specific date minus date of birth equals years

[Guest]

I have a table with dates of birth, I wish to have a report tell me how old
the person will be by years on a specific day. Example: DOB = 2/5/1995 on
2/1/2005 the person will be 9. I have only come close by using DateDiff
and deviding the answer by 365.25. That is the problem, it is only close.
Any thoughts?

 

[Guest]

I have created my own function called Get_Age in a module and I just pass in
the DOB and it returns the age. Mine calculates based on the current date but
I have modified it below to accepts another date as the one to check against
to calculate the age. Where you have used Datediff, substitue with
=Get_Age([DOB],[OtherDate])

Function Get_Age(dtDOB As Date, dtWhen as Date) As Integer
Dim DOBYear, DOBMon, DOBDay, WhenYear, WhenMon, WhenDay,Age1,Age
As Integer

DOBYear = Year(dtDOB)
DOBMon = Month(dtDOB)
DOBDay = Day(dtDOB)
WhenYear = Year(dtWhen)
WhenMon = Month(dtWhen)
WhenDay = Day(dtWhen)

Age1 = WhenYear - DOBYear
Age = IIf(DOBMon < WhenMon, Age1, IIf(DOBMon > WhenMon, Age1 - 1,
IIf(DOBDay <= WhenDay, Age1, Age1 - 1)))

Get_Age = Age
End Function

 

[Van T. Dinh]

You can do minor change to the code from The Access Web to achieve what you
want:

http://www.mvps.org/access/datetime/date0001.htm

 

[John Vinson]

On Tue, 19 Jul 2005 03:44:02 -0700, "just another Mike" <just another

I have a table with dates of birth, I wish to have a report tell me how old
the person will be by years on a specific day. Example: DOB = 2/5/1995 on
2/1/2005 the person will be 9. I have only come close by using DateDiff
and deviding the answer by 365.25. That is the problem, it is only close.
Any thoughts?

A one-liner: as a calculated field in a Query or as the control source
of a textbox, put

DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

John W. Vinson[MVP]

 

[Guest]

I must have missed something.

I changed =Get_Age([DOB],[OtherDate]) to =Get_Age([DOB],[9/1/2005]) and
created a module pasting from "Function........ End Function"

I get a responce of "undefined function, Get_Age"

Do I need to link this module in some way?

I have created my own function called Get_Age in a module and I just pass in
the DOB and it returns the age. Mine calculates based on the current date but
I have modified it below to accepts another date as the one to check against
to calculate the age. Where you have used Datediff, substitue with
=Get_Age([DOB],[OtherDate])

Function Get_Age(dtDOB As Date, dtWhen as Date) As Integer
Dim DOBYear, DOBMon, DOBDay, WhenYear, WhenMon, WhenDay,Age1,Age
As Integer

DOBYear = Year(dtDOB)
DOBMon = Month(dtDOB)
DOBDay = Day(dtDOB)
WhenYear = Year(dtWhen)
WhenMon = Month(dtWhen)
WhenDay = Day(dtWhen)

Age1 = WhenYear - DOBYear
Age = IIf(DOBMon < WhenMon, Age1, IIf(DOBMon > WhenMon, Age1 - 1,
IIf(DOBDay <= WhenDay, Age1, Age1 - 1)))

Get_Age = Age
End Function

I have a table with dates of birth, I wish to have a report tell me how old
the person will be by years on a specific day. Example: DOB = 2/5/1995 on
2/1/2005 the person will be 9. I have only come close by using DateDiff
and deviding the answer by 365.25. That is the problem, it is only close.
Any thoughts?

 

[Douglas J. Steele]

When you created the function in a module, what did you name the module? If
you named the module itself (not the function) Get_Age, that's your problem:
modules can't be named the same as procedures within them.

And assuming you're trying to find their age as of Sept. 1st of this year,
use =Get_Age([DOB],#9/1/2005#)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I must have missed something.

I changed =Get_Age([DOB],[OtherDate]) to =Get_Age([DOB],[9/1/2005]) and
created a module pasting from "Function........ End Function"

I get a responce of "undefined function, Get_Age"

Do I need to link this module in some way?

I have created my own function called Get_Age in a module and I just pass
in
the DOB and it returns the age. Mine calculates based on the current date
but
I have modified it below to accepts another date as the one to check
against
to calculate the age. Where you have used Datediff, substitue with
=Get_Age([DOB],[OtherDate])

Function Get_Age(dtDOB As Date, dtWhen as Date) As Integer
Dim DOBYear, DOBMon, DOBDay, WhenYear, WhenMon, WhenDay,Age1,Age
As Integer

DOBYear = Year(dtDOB)
DOBMon = Month(dtDOB)
DOBDay = Day(dtDOB)
WhenYear = Year(dtWhen)
WhenMon = Month(dtWhen)
WhenDay = Day(dtWhen)

Age1 = WhenYear - DOBYear
Age = IIf(DOBMon < WhenMon, Age1, IIf(DOBMon > WhenMon, Age1 - 1,
IIf(DOBDay <= WhenDay, Age1, Age1 - 1)))

Get_Age = Age
End Function

I have a table with dates of birth, I wish to have a report tell me how
old
the person will be by years on a specific day. Example: DOB =
2/5/1995 on
2/1/2005 the person will be 9. I have only come close by using
DateDiff
and deviding the answer by 365.25. That is the problem, it is only
close.
Any thoughts?

 

[Guest]

Dennis:
I would like to create a NEW field that is based on field that contains the
person's date of birth.
The date of birth field is called "Mrpa99"."D_Birth". I tried your
calculations below and substituted this whole path for Bdate but it doesn't
work. Any ideas?

I have created my own function called Get_Age in a module and I just pass in
the DOB and it returns the age. Mine calculates based on the current date but
I have modified it below to accepts another date as the one to check against
to calculate the age. Where you have used Datediff, substitue with
=Get_Age([DOB],[OtherDate])

Function Get_Age(dtDOB As Date, dtWhen as Date) As Integer
Dim DOBYear, DOBMon, DOBDay, WhenYear, WhenMon, WhenDay,Age1,Age
As Integer

DOBYear = Year(dtDOB)
DOBMon = Month(dtDOB)
DOBDay = Day(dtDOB)
WhenYear = Year(dtWhen)
WhenMon = Month(dtWhen)
WhenDay = Day(dtWhen)

Age1 = WhenYear - DOBYear
Age = IIf(DOBMon < WhenMon, Age1, IIf(DOBMon > WhenMon, Age1 - 1,
IIf(DOBDay <= WhenDay, Age1, Age1 - 1)))

Get_Age = Age
End Function

I have a table with dates of birth, I wish to have a report tell me how old
the person will be by years on a specific day. Example: DOB = 2/5/1995 on
2/1/2005 the person will be 9. I have only come close by using DateDiff
and deviding the answer by 365.25. That is the problem, it is only close.
Any thoughts?

 

[strive4peace]

Hi K,

You should not store age as it always changes and can be
calculated. This was posted by Ricky Hicks

DateDiff("yyyy", [DOBField], Date()) +
(Date() < DateSerial(Year(Date()), Month([DOBField]),
Day([DOBField])))

"DOBField" should be the name of the field that contains the
Date of Birth value ...

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Dennis:
I would like to create a NEW field that is based on field that contains the
person's date of birth.
The date of birth field is called "Mrpa99"."D_Birth". I tried your
calculations below and substituted this whole path for Bdate but it doesn't
work. Any ideas?





:

I have created my own function called Get_Age in a module and I just pass in
the DOB and it returns the age. Mine calculates based on the current date but
I have modified it below to accepts another date as the one to check against
to calculate the age. Where you have used Datediff, substitue with
=Get_Age([DOB],[OtherDate])

Function Get_Age(dtDOB As Date, dtWhen as Date) As Integer
Dim DOBYear, DOBMon, DOBDay, WhenYear, WhenMon, WhenDay,Age1,Age
As Integer

DOBYear = Year(dtDOB)
DOBMon = Month(dtDOB)
DOBDay = Day(dtDOB)
WhenYear = Year(dtWhen)
WhenMon = Month(dtWhen)
WhenDay = Day(dtWhen)

Age1 = WhenYear - DOBYear
Age = IIf(DOBMon < WhenMon, Age1, IIf(DOBMon > WhenMon, Age1 - 1,
IIf(DOBDay <= WhenDay, Age1, Age1 - 1)))

Get_Age = Age
End Function

:

I have a table with dates of birth, I wish to have a report tell me how old
the person will be by years on a specific day. Example: DOB = 2/5/1995 on
2/1/2005 the person will be 9. I have only come close by using DateDiff
and deviding the answer by 365.25. That is the problem, it is only close.
Any thoughts?