auto age calculation from a date of birth in a table

[Guest]

I have created an auto age calculation using access help. It is in forms.
How can i use the calculation in a table?

 

[Graham R Seach]

You can't. You can only perform calculations in SQL or VBA.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Nikos Yannacopoulos]

You should *not* store ages in tables, because they change over time!
Having stored the birth date, you can calculate the current age any time
it is required, in queries/forms/reports.
Generally, you should not store data that can be calculated on the basis
of other stored data; always store the absolute minimum data.

HTH,
Nikos

 

[Joseph Meehan]

I have created an auto age calculation using access help. It is in
forms. How can i use the calculation in a table?

First you can't. Second you should not even try.

The general rule is to never store information in a table that is the
result of a calculation based in information located in that or other
tables. Access is really good a computing the answer again, better than
storing and re-displaying it.

The second reason is because the age will be wrong within a year and
some will be wrong the next day. It you re-compute it each time it will
always be right.

Of course if you want to store the age of a person at a specific date,
then you can use the formula in an update query or other method.

 

[Guest]

How did you create the auto age calculation? I need to do this, too.

 

[John Vinson]

How did you create the auto age calculation? I need to do this, too.

You can calculate the age in a Query by typing:

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

all on one line in a vacant Field cell in a query.

The Age should NOT be stored in a table field. If you store an age
today, it will certainly be WRONG in a year. Just calculate it on the
fly.

John W. Vinson[MVP]

 

[Guest]

I copied the formula exactly into a blank field in my form (which gets it's
data from a table - does that make a difference?) and I get a "#Name?"
showing up in the field. What am I doing wrong?

How did you create the auto age calculation? I need to do this, too.

You can calculate the age in a Query by typing:

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

all on one line in a vacant Field cell in a query.

The Age should NOT be stored in a table field. If you store an age
today, it will certainly be WRONG in a year. Just calculate it on the
fly.

John W. Vinson[MVP]

 

[John Vinson]

I copied the formula exactly into a blank field in my form (which gets it's
data from a table - does that make a difference?) and I get a "#Name?"
showing up in the field. What am I doing wrong?

two possibilities:

- Your date of birth field might have a name different than the [DOB]
which I assumed. If it does, change DOB to whatever the name of your
date of birth field might be.

- If that doesn't work, you might have the very common References bug.
Open the VBA editor by typing Ctrl-G, and select Tools... References.
If any of them are marked MISSING, uncheck that reference, recheck it,
close and open Access and see if the problem resolves.

John W. Vinson[MVP]