View Question
Q: calculate age in years and months from date of birth in MSAccess 2000 query ( Answered ,   1 Comment )
 Question
 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 DateDiff("m",[DateOfBirth],Date()). Thanks, Sylvia101```
 ```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 DateOfBirth 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: MVPS Access FAQ http://www.mvps.org/access/datetime/date0001.htm - 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 ```Sylvia, 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 http://answers.google.com/answers/pricing.html 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```
 ```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```