DateDiff determines how many end-points it crosses. For instance,
DateDiff("yyyy", #12/31/2005#, #1/1/2006#) will return 1, since there's 1
change of year between those two dates
To calculate age, the normal approach is to determine whether or not the
birthday has already occurred in the year. You do this by comparing the
month and day of the Date of Birth to the current month and day and
subtracting 1 from what DateDiff calculates if the birthday hasn't yet
occurred:
DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") < Format([DOB],
"mmdd"), 1, 0)
You have to apply logic like this in all your cases.
You might also check "A More Complete DateDiff Function" at
http://www.accessmvp.com/djsteele/Diff2Dates.html
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"CSSMepps" <(E-Mail Removed)> wrote in message
news:ACECAA92-9D8E-48EC-BF3A-(E-Mail Removed)...
> Need some help to get the results I need. The formula I started with is
this:
> DateDiff: DateDiff([Interval],[LastDateDue],[CurrentDate])
>
> This what I want, but my formula does not return this:
> If interval yyyy, then June 1, 2004 to May 1, 2006 = 1 (this equal to 1
year
> or 365 days)
> If interval yyyy, then June 1, 2005 to May 1, 2006 (this is less than 1
year
> or 365 days) (formula actually returns 1, I want it to be 0)
> If interval m, then April 5, 2006 to May 1, 2006(this is less than a month
> or 30 days) (formula actually returns 1, I need a 0)
> If interval m, then April 1, 2006 to May 1, 2006 = 1 (this is equal to a
> month)
> If interval m, then March 15, 2006 to May 1, 2006 (this is less than two
> months or 60 days) (formula returns 2, I need a 1,)
> If interval m, then March 1, 2006 to May 1, 2006= 2( this is equal to two
> months or 60 days)
>
> I need a formula that returns a whole number up to but not over the
> difference in years or months or quarters. Any suggestions?
>
Similar Threads
