Date of Birth and Age

[Guest]

I know that I have poseted this before, but I still need some clarification.

1. I want to recreate two fields:
a) DOB. What do I put in that field. Short date?
2) AGE: What needs to be but in the field.
2. Then I know I should run a query. What formula and I have to make a
relationship between DOB & AGE?
I hope someone will be able to help me for I am new at this.
I really need a step by step approach.
Thanks for any assistance you can give me.

 

[Guest]

Here is the fast way --
Age: DateDiff("y",[YourDateField], Date())
But if someone was born 15 December and today was 15 january then it says
that difference is 1 year.

Search this group for AGE.

 

[Joseph Meehan]

I know that I have poseted this before, but I still need some
clarification.

1. I want to recreate two fields:
a) DOB. What do I put in that field. Short date?
2) AGE: What needs to be but in the field.

Are you really sure you want to have field "2" In 365¼ days all the
entries will be wrong. I suspect what you really want is a query, form or
report with the calculation so you will always have the correct answer. Of
course if you want to know the age of everyone as of a single date and never
update it, then you would want that field and you would use an UPDATE query
to fill it in the first time.

I don't have the proper formula handy, but I am sure if you search for
birthday you will find it.

 

[Douglas J. Steele]

Only store DOB, as a date. (Format doesn't matter: dates are stored
internally as 8 byte floating point numbers, where the integer part
represents the number of days relative to 30 Dec, 1899)

Create a query that includes a calculated field to compute the age. The
formula to use is:

Age: DateDiff("yyyy", [DOB], Date()) - _
IIf(Format(Date(), "mmdd") < Format([DOB], "mmdd"), 1, 0)

Use the query wherever you would otherwise have used the table.

 

[Joseph Meehan]

Joseph Meehan wrote:
...

I don't have the proper formula handy, but I am sure if you search
for birthday you will find it.

OK I looked it up:

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

 

[John Vinson]

I know that I have poseted this before, but I still need some clarification.

1. I want to recreate two fields:
a) DOB. What do I put in that field. Short date?

A Date/Time field containing the birthdate. The format of this date is
irrelevant - a Date is stored as a number, and you can format it any
way you like.

2) AGE: What needs to be but in the field.

The Age field should NOT EXIST in your table. Calculate it on the fly
instead.

To do so, create a Query based on the table. In a vacant Field cell,
create a calculated field by typing

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

This will calculate the number of New Year's Eve midnights crossed
between the DOB and today's date, and subtract one for those people
whose birthday anniversary has not yet arrived.

John W. Vinson[MVP]

 

[Guest]

I must be doing something wrong because I can't seem to get my list of 145
clients and their ages.

Where do I put this formula? And how can I get my list of people?

I know that I have poseted this before, but I still need some clarification.

1. I want to recreate two fields:
a) DOB. What do I put in that field. Short date?

A Date/Time field containing the birthdate. The format of this date is
irrelevant - a Date is stored as a number, and you can format it any
way you like.

2) AGE: What needs to be but in the field.

The Age field should NOT EXIST in your table. Calculate it on the fly
instead.

To do so, create a Query based on the table. In a vacant Field cell,
create a calculated field by typing

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

This will calculate the number of New Year's Eve midnights crossed
between the DOB and today's date, and subtract one for those people
whose birthday anniversary has not yet arrived.

John W. Vinson[MVP]

 

[John Vinson]

I must be doing something wrong because I can't seem to get my list of 145
clients and their ages.

Where do I put this formula? And how can I get my list of people?

You create a new Query, by opening the Queries tab on the database
window and selecting "New Query". Choose the table (I have no idea
where it is or what it's named, I cannot see your database - you can).
Select whatever other fields you want to see into the row of the query
grid labeled "Fields". This would probably include the people's names,
and whatever other information you wish to see.

Then copy and paste the next two lines, all on one line (it line
breaks on the newsgroup but needs to be all one line on the query)
into the next vacant Field cell in the query grid:

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

Open this Query by clicking the datasheet-image icon at the left of
the toolbar.


John W. Vinson[MVP]

 

[James A. Fortune]

I know that I have poseted this before, but I still need some clarification.

1. I want to recreate two fields:
a) DOB. What do I put in that field. Short date?
2) AGE: What needs to be but in the field.
2. Then I know I should run a query. What formula and I have to make a
relationship between DOB & AGE?
I hope someone will be able to help me for I am new at this.
I really need a step by step approach.
Thanks for any assistance you can give me.

I use the following expression for Age as of CurrentDate:

Int(Format([CurrentDate],"yyyy.mmdd") - Format([BirthDate],"yyyy.mmdd"))

Hopefully you have already received an answer about how and where to use
an expression like this.

James A. Fortune