extracting age

[MissAJ]

I'm a teacher with only basic experience using Access 2000 on Windows XP.
I've created a very simple database of the students in our small school. If I have a field for their birthdates, can I make a field that uses that date, comparing it to the present date, to display the students' ages in an age field?

 

[Andrew Fergus]

Hi

Rather than create a field in your table to store the age, it is better to calculate the age as and when you need by using a query. I'm not sure of your experience with queries but if you need additional guidance on how to create a query let us know.

Once you are in the query design screen and have based the query on your table, add the name and birthdate fields into the query design grid and then add a new field into the query that uses the following (copy and paste this into your query design grid) :

AgeInYears : Year(Date()) - Year([birthdate]) - IIf(Month(Date()) < Month([birthdate]), 1, IIf(Month(Date()) > Month([birthdate]), 0, IIf(Day(Date())<Day([birthdate]), 1, 0)))

This assumes the birthdate field is called 'birthdate' - if it isn't then you need to change it to the actual field name. Save the query and check it provides the results you are expecting (click menu option View > Datasheet View). Any time you want to see the students details and their ages, simply open / run this query.

HTH, Andrew

 

[MissAJ]

Check my steps:
When I am working in the query design screen, I click my "name" field in the first field drop-down list, then I click my "birthdate" field in the next field drop-down list to the right.
How do I add a new field in the query? Type "age" in the space for the next field drop-down list? Where do I paste the code you gave me? Do I save the query by using "Save As"? What do I do in the "Save As" box?

 

[Andrew Fergus]

Hi

Your first two steps are correct. To show the age, instead of selecting anything for the third field, copy the code above, and then paste that into the box where you would ordinarily select the field (like you did with the first two). You don't have to select an item from the drop down list - you can type in anything you want - but rather than trying to type in the code I provided, it will be easier to paste it in. BUT, if you have used a birthdate field with a different name, then you will need to change that within the code to the actual name of your field.

Yes save using the 'Save As' - in the first box enter a useful name (like Students Ages) and leave the second box as is (ie Query).

HTH, Andrew

 

[MissAJ]

Thanks again. Your explanation was clear, and it works well. Thanks for taking your time to help.


Questions Answered at http://www.mwtb.org/pages/QA.php