On Thu, 2 Feb 2006 12:56:28 -0800, Smohrman wrote:
See comments interspersed below as well as at the end of this message.
Thanks for that clarification. I am now working with a query, and have
gotten some code working that returns a value of months since a date:
Age in Months: DateDiff("m",[AquisitionDate],Now())
This gives us the age of the item in months. Ideally Iÿd like to use
something like this:
Age=DateDiff("yyyy", [AquisitionDate], Now())+ _
Int( Format(now(), "mmdd") < Format( [AquisitionDate], "mmdd") )
which should give the age in Years/Months,
No it will not give you the age in years and months.
And that is not how you write and expression in a query.
but when I try it I get an error
stating that I ´Omitted an operand or operator, used an invalid character or
comma, or didnÿt surround text with quotes...¡ I tried changing Age= to Age:
DateDiff like in the code above which works but that didnÿt fix it.
If you are in the query grid you do not start with the = sign. Use:
Age
ateDiff(etc....)
More comments at the end.....
1) To accurately compute Age by year, you must take into account
whether or not that date has already occurred in the current year.
DateDiff by itself does not take that into account, i.e.
DateDiff("yyyy",#12/31/2005#,#1/1/2006#) will = 1.
For someone born on 9/15/1980, the DateDiff function will return
either years, (26), or months, (305), or days ( 9271), depending upon
the argument you supply it. (Note that the actual age in years should
be 25, not 26, as the birth date has not yet occurred this year).
To accurately calculate someone's age in years only, the following
will be accurate.
In a query:
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)
2) You are using Now() to represent the current date. Now() includes a
time of day value and you may get different results depending upon the
actual time of day you run the query. Always use Date() if the time of
day is not needed.
3) The post I responded to had a reference to Doug Steele's Diff2Dates
function at:
http://www.accessmvp.com/djsteele/Diff2Dates.html
not the DateDiff function.
The Diff2Date function will return the age in years and months and
days, all at once, depending upon the arguments you supply it.
Some one born on 9/15/1980 will be
"25 years 4 months 18 days" old today.
Isn't that what you want according to your message subject line?
If you want that then you must use the Diff2Dates function.
Download the function and copy it to a Module.
Refer to it in your query grid like this:
AgeToday
iff2Dates("ymd",[DateOfBirth],Date(),True)
or like this (notice the different results):
AgeToday
iff2Dates("ymd",[ADate],Date())
Good luck!
