calculate age in years, months, days

  • Thread starter Thread starter Grams
  • Start date Start date
G

Grams

In that order - years, months, days
=DateDiff("mmddyyyy",[BirthDate],Now())+(Format([BirthDate],"mmddyyyy")>Format(Now(),"mmddyyyy"))
I got the above from a post but when I try it I get #Error

Help
Janet
 
"mmddyyyy" is not valid for the Interval argument for the DateDiff() function!
You have to choose a single date component, and for this kind of thing it has
to the the lowest common denominator, Day.

You then have to parse the years/months/days out based on the days in a year,
days in a month and the remaining days. Because all months are not created
equal, some having 30 days, some having 31 days and February having varying
days, depending on whether or not it's a leap year, you'll have to compromise
and decide which on value to use for days in a month in order to do this.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
 
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
Okay. This is what I got from the link you sent me to. I don't want those
particular dates in there. My field is called [Birthdate]. So how would that
look calculating from today's date and using my fieldname. I tried replacing
the 06/01/1998 with the field and 06/26/2002 with Now() and Date(), but got
errors on all tries. Do I need the # signs in there? It won't work no
matter what I try

Janet

fredg said:
In that order - years, months, days
=DateDiff("mmddyyyy",[BirthDate],Now())+(Format([BirthDate],"mmddyyyy")>Format(Now(),"mmddyyyy"))
I got the above from a post but when I try it I get #Error

Help
Janet
Click to expand...

Check "A More Complete DateDiff Function" at
http://www.accessmvp.com/djsteele/Diff2Dates.html
Click to expand...
 
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
Okay. This is what I got from the link you sent me to. I don't want those
particular dates in there. My field is called [Birthdate]. So how would that
look calculating from today's date and using my fieldname. I tried replacing
the 06/01/1998 with the field and 06/26/2002 with Now() and Date(), but got
errors on all tries. Do I need the # signs in there? It won't work no
matter what I try

Janet

fredg said:
In that order - years, months, days
=DateDiff("mmddyyyy",[BirthDate],Now())+(Format([BirthDate],"mmddyyyy")>Format(Now(),"mmddyyyy"))
I got the above from a post but when I try it I get #Error

Help
Janet
Click to expand...

Check "A More Complete DateDiff Function" at
http://www.accessmvp.com/djsteele/Diff2Dates.html
Click to expand...
Click to expand...


It does work.
In my Debug Window using your dates:
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
4 years 0 months 25 days

Using your [Birthdate] field .....
In your query add a new column.
ElapsedPeriod:Diff2Dates("ymd", [Birthdate], Date(), True)

If you wish to do this directly on a form or Report, then add an
unbound control.
Set it's control source to:
= Diff2Dates("ymd", [Birthdate], Date(), True)

Using my data, with a Birthdate value of 5/24/2007, my text control
value is "0 years 8 months 7 days" from then to today.

Change the True to False and the value is
8 months 7 days.

Open the module into which you pasted the function.
Click on Debug + Compile
It should not return any errors.
Make sure the name of the module into which you pasted the function
does not have the same name as the function (i.e. NOT Diff2Dates.
mdlDiff2Dates is OK).
Now try it again in a query.
 
Back
Top