This assumes that you have a table called tblBirthdays. The table has
1. Name - Text
2. DateOfBirth - Date
This query will produce a result set with 6 fields:
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
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.
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
Clarification of Answer by
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!