How do I calculate an age in Access?

[Guest]

I have a Birthdate field and an InsAge field. I want the InsAge field to
contain the calculation of the age based on the Birthdate. I used the
DateDiff formula, but only received a "O" result.

DateDiff("yyyy",[Birthdate],#04/01/2005#)+(Format([Birthdate],"mmdd")>Format
(#04/01/2005#,"mmdd"))

In a nutshell, if the birthdate field has 2/10/40, I want the InsAge field
to calculate 65. Help....

 

[Guest]

Hi.

In general, you don't want to store a calculation in a table, because
information that the calculations are based upon can change, rendering the
calculated data in the table inaccurate. This can become a maintenance
nightmare trying to update all of the relevant calculations, and leads to
loss of data integrity, where some records are correct, but other records are
wrong. No one can trust the some of the data, but no one knows exactly which
data is untrustworthy.

That said, if you need to save a person's age on a specific date, say April
1, 2005, and that date isn't going to _ever_ change, you could use the
following query to set the person's age in the InsAge column in the table:

UPDATE MyTable
SET InsAge = (DateDiff("yyyy", Birthdate, #04/01/2005#) + Int("0401" <
Format(Birthdate, "mmdd")));

But it would be much better to use the following query to just display the
person's age on April 1, 2005, so if the date ever changed, only the query
need be changed, not any records:

SELECT (DateDiff("yyyy", Birthdate, #04/01/2005#) + Int("0401" <
Format(Birthdate, "mmdd"))) AS InsAge
FROM MyTable;

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.

 

[Guest]

I tried the following method:

UPDATE MyTable

SET InsAge = (DateDiff("yyyy", Birthdate, #04/01/2005#) + Int("0401" <
Format(Birthdate, "mmdd")));

and I'm receiving an "undefined format" message.

I should tell you that I had change Birthdate to DOB and Set InsAge to
Ins_Age.

I'm very basic with building expressions with Access. I'm not sure am I
suppose to include the Set InsAge portion, if so, I did. I also tried
without it and still no luck.

Any suggestions.

Hi.

In general, you don't want to store a calculation in a table, because
information that the calculations are based upon can change, rendering the
calculated data in the table inaccurate. This can become a maintenance
nightmare trying to update all of the relevant calculations, and leads to
loss of data integrity, where some records are correct, but other records are
wrong. No one can trust the some of the data, but no one knows exactly which
data is untrustworthy.

That said, if you need to save a person's age on a specific date, say April
1, 2005, and that date isn't going to _ever_ change, you could use the
following query to set the person's age in the InsAge column in the table:

UPDATE MyTable
SET InsAge = (DateDiff("yyyy", Birthdate, #04/01/2005#) + Int("0401" <
Format(Birthdate, "mmdd")));

But it would be much better to use the following query to just display the
person's age on April 1, 2005, so if the date ever changed, only the query
need be changed, not any records:

SELECT (DateDiff("yyyy", Birthdate, #04/01/2005#) + Int("0401" <
Format(Birthdate, "mmdd"))) AS InsAge
FROM MyTable;

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.

I have a Birthdate field and an InsAge field. I want the InsAge field to
contain the calculation of the age based on the Birthdate. I used the
DateDiff formula, but only received a "O" result.

DateDiff("yyyy",[Birthdate],#04/01/2005#)+(Format([Birthdate],"mmdd")>Format
(#04/01/2005#,"mmdd"))

In a nutshell, if the birthdate field has 2/10/40, I want the InsAge field
to calculate 65. Help....

 

[John Vinson]

I have a Birthdate field and an InsAge field. I want the InsAge field to
contain the calculation of the age based on the Birthdate. I used the
DateDiff formula, but only received a "O" result.

DateDiff("yyyy",[Birthdate],#04/01/2005#)+(Format([Birthdate],"mmdd")>Format
(#04/01/2005#,"mmdd"))

In a nutshell, if the birthdate field has 2/10/40, I want the InsAge field
to calculate 65. Help....

So do you want to calculate the age *as of April 1 2005*, whenever you
run the query? I.e. if you run the query five years from now, do you
want the answer to be 65 or 70 for that birthdate? What's the
importance of April 1 - was that a random example, or do you really
want the age as of today's date?

Gunny's absolutely right: this value SHOULD NOT EXIST in your table.
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.

If you can clarify just what "age" you want, the expression Gunny
proposed can be adapted to the age that you seek.

John W. Vinson[MVP]

 

[Guest]

Hi.

Try:

UPDATE MyTable
SET Ins_Age = (DateDiff("yyyy", DOB, #04/01/2005#) + Int("0401" <
Format(DOB, "mmdd")));

.... and replace MyTable with the name of your table.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.

I tried the following method:

UPDATE MyTable

SET InsAge = (DateDiff("yyyy", Birthdate, #04/01/2005#) + Int("0401" <
Format(Birthdate, "mmdd")));

and I'm receiving an "undefined format" message.

I should tell you that I had change Birthdate to DOB and Set InsAge to
Ins_Age.

I'm very basic with building expressions with Access. I'm not sure am I
suppose to include the Set InsAge portion, if so, I did. I also tried
without it and still no luck.

Any suggestions.

Hi.

In general, you don't want to store a calculation in a table, because
information that the calculations are based upon can change, rendering the
calculated data in the table inaccurate. This can become a maintenance
nightmare trying to update all of the relevant calculations, and leads to
loss of data integrity, where some records are correct, but other records are
wrong. No one can trust the some of the data, but no one knows exactly which
data is untrustworthy.

That said, if you need to save a person's age on a specific date, say April
1, 2005, and that date isn't going to _ever_ change, you could use the
following query to set the person's age in the InsAge column in the table:

UPDATE MyTable
SET InsAge = (DateDiff("yyyy", Birthdate, #04/01/2005#) + Int("0401" <
Format(Birthdate, "mmdd")));

But it would be much better to use the following query to just display the
person's age on April 1, 2005, so if the date ever changed, only the query
need be changed, not any records:

SELECT (DateDiff("yyyy", Birthdate, #04/01/2005#) + Int("0401" <
Format(Birthdate, "mmdd"))) AS InsAge
FROM MyTable;

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.

I have a Birthdate field and an InsAge field. I want the InsAge field to
contain the calculation of the age based on the Birthdate. I used the
DateDiff formula, but only received a "O" result.

DateDiff("yyyy",[Birthdate],#04/01/2005#)+(Format([Birthdate],"mmdd")>Format
(#04/01/2005#,"mmdd"))

In a nutshell, if the birthdate field has 2/10/40, I want the InsAge field
to calculate 65. Help....