Access 2007

[Pete]

I have two fields in my table view, Birthday and Age, is there any way to
automatically update the age from the birthday field? maybe even keep it
current when i open my database?

 

[Douglas J. Steele]

Age should not be stored in the table: only Birthday should.

Instead of storing Age, you should calculate it in a query (you can use
DateDiff("yyyy", [Birthday], Date()) - IIf(Format(Date(), "mmdd") <
Format([Birthday], "mmdd"), 1, 0)), then use the query wherever you would
otherwise have used the table.

 

[Pete]

That would work, but for instance, i need a list of everyone 26 and under,
for defensive driving classes, and i need a separate list for everyone under
21 wouldnt i just be making more work for myself?

Age should not be stored in the table: only Birthday should.

Instead of storing Age, you should calculate it in a query (you can use
DateDiff("yyyy", [Birthday], Date()) - IIf(Format(Date(), "mmdd") <
Format([Birthday], "mmdd"), 1, 0)), then use the query wherever you would
otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have two fields in my table view, Birthday and Age, is there any way to
automatically update the age from the birthday field? maybe even keep it
current when i open my database?

 

[Douglas J. Steele]

To get a list of everyone 26 and under, create a query with the following
criteria for Birthday

<= DateAdd("yyyy", -26, Date())

No need to calculate age!

For everyone under 21, use < DateAdd("yyyy", -21, Date())

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

That would work, but for instance, i need a list of everyone 26 and under,
for defensive driving classes, and i need a separate list for everyone
under
21 wouldnt i just be making more work for myself?

Age should not be stored in the table: only Birthday should.

Instead of storing Age, you should calculate it in a query (you can use
DateDiff("yyyy", [Birthday], Date()) - IIf(Format(Date(), "mmdd") <
Format([Birthday], "mmdd"), 1, 0)), then use the query wherever you would
otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have two fields in my table view, Birthday and Age, is there any way
to
automatically update the age from the birthday field? maybe even keep
it
current when i open my database?

 

[John W. Vinson]

That would work, but for instance, i need a list of everyone 26 and under,
for defensive driving classes, and i need a separate list for everyone under
21 wouldnt i just be making more work for myself?

No. You'ld be making it EASIER for yourself.

You should not store the age in any 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. In fact, for an age, you can be absolutely
certain that every record in your table will have an incorrect age within a
year!

Just redo the calculation whenever you need it as a calculated field in a
Query; you can apply criteria such as >26, or >21, or > [Enter cutoff age:] to
get a prompt, or > [Forms]![YourForm]![txtCutoff] to pull the criterion from a
form. It is NOT necessary to store the data redundantly in your table.

If you're routinely working in table datasheets... don't. That's not their
purpose! Use Queries and Forms, the tools that Access provides for working
with the data in tables.