Calculating an age automatically each year

[Guest]

i have a field with date of birth and one with ages. I would like for the age
field to update automatically. For example: Jessica was born 4/7/1979(DOB
field) she is know 26(age field) but next year on the same date i would
like it to automatically change 27. What can i do?

 

[Joseph Meehan]

i have a field with date of birth and one with ages. I would like for
the age field to update automatically. For example: Jessica was born
4/7/1979(DOB field) she is know 26(age field) but next year on the
same date i would like it to automatically change 27. What can i do?

First you get rid of that age field. Never store computed numbers.

You compute the age each time you need it.

age: DateDiff("yyyy",[birth],Date())

birth = name of birthday field (must be an Access date filed.

 

[Rick Brandt]

i have a field with date of birth and one with ages. I would like for
the age field to update automatically. For example: Jessica was born
4/7/1979(DOB field) she is know 26(age field) but next year on the
same date i would like it to automatically change 27. What can i do?

Eliminate the Age field from your table. That data is *derived* from the
date of birth and as such it should be calculated on the fly in a query,
form, or report so it is always correct. Here is what I recommend...

Delete the field [Age] from the table.

Create a new SELECT query based on your table and include all the fields
from the table. Then add one additional calculated field to the query...

Age: DateDiff("yyyy", [DateOfBirth], Date()) + (Format([DateOfBirth],"mmdd")

Format(Date(),"mmdd"))

When you look at the results of the query it will look and act just like a
table that has an Age field except that Age will always be correct (because
it is not stored). Now you simply use this query instead of your table in
any form, report, or other query where the Age field is required

 

[Guest]

I get an error message and it also tells me i have to many parantheses

i have a field with date of birth and one with ages. I would like for
the age field to update automatically. For example: Jessica was born
4/7/1979(DOB field) she is know 26(age field) but next year on the
same date i would like it to automatically change 27. What can i do?

Eliminate the Age field from your table. That data is *derived* from the
date of birth and as such it should be calculated on the fly in a query,
form, or report so it is always correct. Here is what I recommend...

Delete the field [Age] from the table.

Create a new SELECT query based on your table and include all the fields
from the table. Then add one additional calculated field to the query...

Age: DateDiff("yyyy", [DateOfBirth], Date()) + (Format([DateOfBirth],"mmdd")

Format(Date(),"mmdd"))

When you look at the results of the query it will look and act just like a
table that has an Age field except that Age will always be correct (because
it is not stored). Now you simply use this query instead of your table in
any form, report, or other query where the Age field is required

 

[Rick Brandt]

I get an error message and it also tells me i have to many parantheses

Are you sure line-wrapping didn't mess up what I posted? I did test that
expression in the Immediate window before I posted it and it worked for me
(substituting an actual date value for [DateOfBirth]). Otherwise post the
entire SQL of your query and someone will figure out what is wrong with it

 

[Joseph Meehan]

I get an error message and it also tells me i have to many parantheses

i have a field with date of birth and one with ages. I would like
for the age field to update automatically. For example: Jessica
was born 4/7/1979(DOB field) she is know 26(age field) but next
year on the same date i would like it to automatically change 27.
What can i do?

Eliminate the Age field from your table. That data is *derived*
from the date of birth and as such it should be calculated on the
fly in a query, form, or report so it is always correct. Here is
what I recommend...

Delete the field [Age] from the table.

Create a new SELECT query based on your table and include all the
fields from the table. Then add one additional calculated field to
the query...

Age: DateDiff("yyyy", [DateOfBirth], Date()) +
(Format([DateOfBirth],"mmdd")

Format(Date(),"mmdd"))

I could be wrong but it looks like one too many; try deleting the last
")"

 

[Douglas J. Steele]

Age: DateDiff("yyyy", [DateOfBirth], Date()) +
(Format([DateOfBirth],"mmdd")
Format(Date(),"mmdd"))

I could be wrong but it looks like one too many; try deleting the last
")"

They look right to me. One closes the Format function, and the other closes
the opening parenthesis in front of the first Format statement.

 

[Joseph Meehan]

Age: DateDiff("yyyy", [DateOfBirth], Date()) +
(Format([DateOfBirth],"mmdd")
Format(Date(),"mmdd"))

I could be wrong but it looks like one too many; try deleting the
last ")"

They look right to me. One closes the Format function, and the other
closes the opening parenthesis in front of the first Format statement.

Age: DateDiff("yyyy", [DateOfBirth], Date()) +
(Format([DateOfBirth],"mmdd") > Format(Date(),"mmdd"))

Without checking syntax I would have to guess that there is one too many
or one too few as it appears the last parenthesis pair is closing two format
statements (first one already already closed, or the second statement is
lacking an opening parenthesis.

I seldom use the format statement so if you say you are right, I will
not argue.

 

[Douglas J. Steele]

Age: DateDiff("yyyy", [DateOfBirth], Date()) +
(Format([DateOfBirth],"mmdd")
Format(Date(),"mmdd"))

I could be wrong but it looks like one too many; try deleting the
last ")"

They look right to me. One closes the Format function, and the other
closes the opening parenthesis in front of the first Format statement.

Age: DateDiff("yyyy", [DateOfBirth], Date()) +
(Format([DateOfBirth],"mmdd") > Format(Date(),"mmdd"))

Without checking syntax I would have to guess that there is one too
many or one too few as it appears the last parenthesis pair is closing two
format statements (first one already already closed, or the second
statement is lacking an opening parenthesis.

Statement A is Format([DateOfBirth], "mmdd")
Statement B is Format(Date(), "mmdd")
The last line is (Statement A > Statement B)

In other words, it's comparing the results of the two Format statements
inside of parentheses. The results of that comparison will be 0 if it's
False, or -1 if it's True.

To make it a little clearer, I prefer to use

Age: DateDiff("yyyy", [DateOfBirth], Date()) -
IIf (Format([DateOfBirth],"mmdd") > Format(Date(),"mmdd"), 1, 0)

In other words, if the birthday hasn't happened yet this year, subtract 1
from the DateDiff result.

 

[Joseph Meehan]

Age: DateDiff("yyyy", [DateOfBirth], Date()) +
(Format([DateOfBirth],"mmdd")
Format(Date(),"mmdd"))

I could be wrong but it looks like one too many; try deleting
the last ")"


They look right to me. One closes the Format function, and the other
closes the opening parenthesis in front of the first Format
statement.

Age: DateDiff("yyyy", [DateOfBirth], Date()) +
(Format([DateOfBirth],"mmdd") > Format(Date(),"mmdd"))

Without checking syntax I would have to guess that there is one
too many or one too few as it appears the last parenthesis pair is
closing two format statements (first one already already closed, or
the second statement is lacking an opening parenthesis.

Statement A is Format([DateOfBirth], "mmdd")
Statement B is Format(Date(), "mmdd")
The last line is (Statement A > Statement B)

I got you. Thanks. Now if I can only remember that if I need to use
it.

In other words, it's comparing the results of the two Format
statements inside of parentheses. The results of that comparison will
be 0 if it's False, or -1 if it's True.

To make it a little clearer, I prefer to use

Age: DateDiff("yyyy", [DateOfBirth], Date()) -
IIf (Format([DateOfBirth],"mmdd") > Format(Date(),"mmdd"), 1, 0)

In other words, if the birthday hasn't happened yet this year,
subtract 1 from the DateDiff result.

 

[Douglas J. Steele]

Age: DateDiff("yyyy", [DateOfBirth], Date()) +
(Format([DateOfBirth],"mmdd") > Format(Date(),"mmdd"))

Without checking syntax I would have to guess that there is one
too many or one too few as it appears the last parenthesis pair is
closing two format statements (first one already already closed, or
the second statement is lacking an opening parenthesis.

Statement A is Format([DateOfBirth], "mmdd")
Statement B is Format(Date(), "mmdd")
The last line is (Statement A > Statement B)

I got you. Thanks. Now if I can only remember that if I need to use
it.

You can always look it up at
http://www.mvps.org/access/datetime/date0001.htm at "The Access Web"

 

[James A. Fortune]

i have a field with date of birth and one with ages. I would like for the age
field to update automatically. For example: Jessica was born 4/7/1979(DOB
field) she is know 26(age field) but next year on the same date i would
like it to automatically change 27. What can i do?

In:

http://groups-beta.google.com/group/comp.databases.ms-access/msg/5228de32cbb27372?hl=en&

I give an alternate function to do this.

James A. Fortune