calculate ages in Access based on specific date

[Guest]

Within the report function of Access, what is the formula for calculating age
based on a specific day (i.e., Birtdate is December 12, 2001 and what is the
age based on the date of July 1, 2004).

 

[Rick B]

To calculate the current age...

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







To calcualte from a specific date, just replace both instances of "Date()"
with the field that contains the date you want to compare or with the set
date if that is applicable...





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





Rick B

 

[Van T. Dinh]

See the last method in:

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

 

[Guest]

I'm a novice at formulas in Access and I'm not getting the age based on the
formula I typed:

=DateDiff("yyyy",[Birthdate],(7/1/2004))+(Format([Birthdate],"mmdd")>Format((7/1/2004),"mmdd"))

Also, I may not have been clear in my initial post. I want the age of a
student based on the month of July. Can the formula be writte to always base
it on the month of July. For instance the school year runs from July to
June. If the student is born April 1, 1999, I'm looking to get the age as of
July 1, 2004.

Can you advise what I'm doing wrong and help me correct the formula?

To calculate the current age...

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







To calcualte from a specific date, just replace both instances of "Date()"
with the field that contains the date you want to compare or with the set
date if that is applicable...





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





Rick B

Within the report function of Access, what is the formula for calculating age
based on a specific day (i.e., Birtdate is December 12, 2001 and what is the
age based on the date of July 1, 2004).

 

[Douglas J. Steele]

Dates must be delimited with # characters:

=DateDiff("yyyy",[Birthdate],#7/1/2004#)+(Format([Birthdate],"mmdd")>Format(#7/1/2004#,"mmdd"))

or, since the date is hard-coded,

=DateDiff("yyyy",[Birthdate],#7/1/2004#)+(Format([Birthdate],"mmdd")>"0701")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm a novice at formulas in Access and I'm not getting the age based on
the
formula I typed:

=DateDiff("yyyy",[Birthdate],(7/1/2004))+(Format([Birthdate],"mmdd")>Format((7/1/2004),"mmdd"))

Also, I may not have been clear in my initial post. I want the age of a
student based on the month of July. Can the formula be writte to always
base
it on the month of July. For instance the school year runs from July to
June. If the student is born April 1, 1999, I'm looking to get the age as
of
July 1, 2004.

Can you advise what I'm doing wrong and help me correct the formula?

To calculate the current age...

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







To calcualte from a specific date, just replace both instances of
"Date()"
with the field that contains the date you want to compare or with the set
date if that is applicable...





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





Rick B

Within the report function of Access, what is the formula for
calculating age
based on a specific day (i.e., Birtdate is December 12, 2001 and what
is the
age based on the date of July 1, 2004).

 

[Guest]

Thank you

To calculate the current age...

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







To calcualte from a specific date, just replace both instances of "Date()"
with the field that contains the date you want to compare or with the set
date if that is applicable...





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





Rick B

Within the report function of Access, what is the formula for calculating age
based on a specific day (i.e., Birtdate is December 12, 2001 and what is the
age based on the date of July 1, 2004).

 

[Guest]

Thank you to all for your help to my first question. To take it one step
further, is it possible for the result of the calculated formula to print age
as: 10 year 2 months?

 

[Van T. Dinh]

If this case, you may want to work out the difference in number of months
(using DateDiff function) and then convert it to a text string with years
and months.

 

[Guest]

I have tried to get this to work, but after I enter the expression and close
the field's property box, it removes the expression. I've tried the VB module
version, but I don't know enough to make it work for me. What am I doing
wrong? I have a record for DOB, and field in the form that I want to update
with age when the DOB is entered. I tried putting the expression in the
controlsource for the AGE field.

 

[John Vinson]

I have tried to get this to work, but after I enter the expression and close
the field's property box, it removes the expression. I've tried the VB module
version, but I don't know enough to make it work for me. What am I doing
wrong? I have a record for DOB, and field in the form that I want to update
with age when the DOB is entered. I tried putting the expression in the
controlsource for the AGE field.

Your table *SHOULD NOT HAVE* an AGE field.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

Just set the Control Source of the unbound textbox on the Form to

=DateDiff...

using the age expression from this thread.

John W. Vinson[MVP]