extracting age

MissAJ

Board Regular
Joined
Sep 22, 2006
Messages
86
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?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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) :

Code:
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

Board Regular
Joined
Sep 22, 2006
Messages
86
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
 

Forum statistics

Threads
1,137,366
Messages
5,681,074
Members
419,950
Latest member
BeckiJae

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top