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