Google Answers Logo
View Question
Q: calculate age in years and months from date of birth in MSAccess 2000 query ( Answered 5 out of 5 stars,   1 Comment )
Subject: calculate age in years and months from date of birth in MSAccess 2000 query
Category: Computers > Programming
Asked by: sylvia101-ga
List Price: $4.50
Posted: 10 Feb 2003 10:01 PST
Expires: 12 Mar 2003 10:01 PST
Question ID: 159532
Using DateDiff and expression builder I have calculated age of clients
in years (e.g. 20 years) or in months (e.g. 249 months).  How can I
calculate age in months and years (e.g. 20 years 9 months) then sort
by month so that I can send them birthday greetings every year. I used
Age: DateDiff("yyyy",[DateOfBirth],Date()) and
Thanks,  Sylvia101
Subject: Re: calculate age in years and months from date of birth in MSAccess 2000 query
Answered By: hammer-ga on 10 Feb 2003 11:06 PST
Rated:5 out of 5 stars
This assumes that you have a table called tblBirthdays. The table has
two fields:
1. Name - Text
2. DateOfBirth - Date

This query will produce a result set with 6 fields:
1. Name

2. DateofBirth

3. BirthMonth - Sorted Ascending - Uses the Month function to get the
birth month from the DateOfBirth

4. TotalMonths - Uses DateDiff to get the total number of months since

5. Years - Gets the number of Years by using the Int function to
return the integer portion of the number of months divided by 12.

6. Months - Gets the number of Months by using Mod to return the
remainder of the number of months divided by 12.

Here is the query:

SELECT tblBirthdays.Name, tblBirthdays.DateOfBirth, 
Month([DateOfBirth]) AS BirthMonth, 
DateDiff("m",[DateOfBirth],Date()) AS TotalMonths,
Int(DateDiff("m",[DateOfBirth],Date())/12) AS Years,
DateDiff("m",[DateOfBirth],Date()) Mod 12 AS Months
FROM tblBirthdays
ORDER BY Month([DateOfBirth]);

This should provide an example of how to work with this kind of date
math. Please let me know if you need clarification.

Additional resources:
I followed your requests for the date calculations, but, if you want a
more thorough method of calculating someone's age using their
birthdate, take a look at this article:

- Hammer

Request for Answer Clarification by sylvia101-ga on 11 Feb 2003 07:33 PST
Thanks for the code.  It is very clear and I can follow the logic. 
Forgive my ignorance but I am very new to this and I really don't know
where to put the code.  I want to print out a report with the
birthdate and age on it.  I do have a client table with Name and DOB
fields.  I thought I'd make a query to work out the Age then base the
report on that query but, once again, I don't know how to get an SQL
SELECT statement into the query or where to put it in the code window,
expression builder or ???
If you can help me great.  If it's too complicated to explain by
e-mail then great too ... I'll certainly keep the code for when I'm
able to use it.
Thanks again,   Sylvia

Clarification of Answer by hammer-ga on 11 Feb 2003 08:42 PST

I am certainly willing to create a step-by-step/click-by-click guide
to how to create your report, but the effort required goes well beyond
the amount of work associated with your question price.

Google Answers Pricing Guidelines

If you post another question regarding this, I will be glad to answer
it. If you mention my name (Hammer) in the question title, other
Researchers will leave it for me, but this is not required if you
don't mind a different Researcher answering your question.

If you do decide to post another question, please include the name of
the table you use and the names of the table fields you want to appear
in the report.  That way, we can create a cut-and-paste solution for
you, based on your own database. Also, if you reference this question
(ID #159532), we will be able to have the information provided here.

I'll keep an eye out for your question!

- Hammer
sylvia101-ga rated this answer:5 out of 5 stars

Subject: Re: calculate age in years and months from date of birth in MSAccess 2000 query
From: wolvies-ga on 10 Feb 2003 10:06 PST
I am not qualified to answer :) but the Advanced Filter/Sort option
allows you to specify criteria 1 and criteria 2 so it could sort by
years then by months

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy