date of birth and any dates

[Guest]

Hi, i create a table with inf. of students of special education. some of the fields are dates. How to obtain the age of a student base on the date of birth. I type in the field? Example: 12-sep-1985 (years). My wife and I work for department of education in PR and need a database for a lot of student in our school and this subject its critical. Please any help welcome. we are new in access. I only have the first table. Whats next?

 

[JulieD]

Hi Andrew

to get the age of a student you need to go to the query object section of
the database container - and then choose Create Query in Design View
now add your table and then click on Close
now double click on the student's name field(s) and they will go down the
bottom of the query grid
now double click on the student's date of birth field
now in the column next to the student's date of birth field type

Age: (Now()-[dob])/365

where [dob] is the name of the field that you've stored the student's
birthdate in (you need the brackets if there are spaces in the field
names) - this will give you the age to a large number of decimal places, it
can be modified to no decimal places by using

Age: Int((Now()-[dob])/365)

once you've typed this in press the red exclaimation mark to see the results
of your query.

These results can then form the basis of a report or can be printed from the
query window.

Please note - when you are in the query results window 9 times out of 10 the
data is "live" - this means if you delete it here you will delete it from
your table. So if you only wanted the ages of the girls and not the boys to
be displayed (for example) then modify your query to only show the girls,
rather than deleting the boys.

Another thing - you said that your database only has one table at the moment
.... i would, if i was going to use Access instead of Excel - focus on what
you need in your other tables before you get too far down the query / forms
& reports road as you will possibly have to change these when you sort out
the table structure. With Access (IMHO) everything really needs to start
with getting the tables right first.

Hope this helps
Regards
JulieD

Hi, i create a table with inf. of students of special education. some of

the fields are dates. How to obtain the age of a student base on the date of
birth. I type in the field? Example: 12-sep-1985 (years). My wife and I work
for department of education in PR and need a database for a lot of student
in our school and this subject its critical. Please any help welcome. we
are new in access. I only have the first table. Whats next?

 

[Douglas J. Steele]

While that probably will return accurate enough answers for school ages,
because there are more than 365 days in a year, over time using that
approach will introduce inaccuracies in the reported age.

Far better is to use the following:

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

The reason for the second part is that DateDiff is a little too literal. It
will treat 31 Dec, 2003 to 1 Jan, 2004 as being 1 year. You need to adjust
its calculation by determining whether or not the birthday has taken place
in the current year.

If you need better resolution, such as age in year, month and day, check out
A More Complete DateDiff function at
http://members.rogers.com/douglas.j.steele/Diff2Dates.html

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi Andrew

to get the age of a student you need to go to the query object section of
the database container - and then choose Create Query in Design View
now add your table and then click on Close
now double click on the student's name field(s) and they will go down the
bottom of the query grid
now double click on the student's date of birth field
now in the column next to the student's date of birth field type

Age: (Now()-[dob])/365

where [dob] is the name of the field that you've stored the student's
birthdate in (you need the brackets if there are spaces in the field
names) - this will give you the age to a large number of decimal places, it
can be modified to no decimal places by using

Age: Int((Now()-[dob])/365)

once you've typed this in press the red exclaimation mark to see the results
of your query.

These results can then form the basis of a report or can be printed from the
query window.

Please note - when you are in the query results window 9 times out of 10 the
data is "live" - this means if you delete it here you will delete it from
your table. So if you only wanted the ages of the girls and not the boys to
be displayed (for example) then modify your query to only show the girls,
rather than deleting the boys.

Another thing - you said that your database only has one table at the moment
... i would, if i was going to use Access instead of Excel - focus on what
you need in your other tables before you get too far down the query / forms
& reports road as you will possibly have to change these when you sort out
the table structure. With Access (IMHO) everything really needs to start
with getting the tables right first.

Hope this helps
Regards
JulieD

Hi, i create a table with inf. of students of special education. some of

the fields are dates. How to obtain the age of a student base on the date of
birth. I type in the field? Example: 12-sep-1985 (years). My wife and I work
for department of education in PR and need a database for a lot of student
in our school and this subject its critical. Please any help welcome. we
are new in access. I only have the first table. Whats next?