DateDiff("yyyy",[Anniversary],Now()) returns rounded number

[apex77]

I am using DateDiff("yyyy",[Anniversary],Now()) in a query to return the
number of years between two dates. Since it is an Anniversary calculation,
the result needs to NOT round up. Currently, when the result is 11.73, it
rounds up to 12 when I really need it to stay at 11 until the anniversary
date is reached. Thanks in advance for any help.

 

[John Spencer]

First of all DateDiff returns integer values and never fractional values.

DateDiff returns the number of transitions that occur. So Dec 31 2008 to Jan 1
2009 will return 1 year even though only one day has elapsed.

The number of whole years can be calculated with a slightly more complex
expression. The one below is generally reliable. It takes into account month
and day as well as year.

DateDiff("yyyy",Anniversary,Date()) +
Int(Format(Anniversary,"mmdd") > Format(Date(),"mmdd"))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[vanderghast]

DateDiff( boundary, ... ) returns the number of time the boundary is
crossed over. It is always an integer.

See http://www.mvps.org/access/datetime/date0001.htm for many variations,
in addition to


Age=DateDiff("yyyy", [Bdate], Now())+ _
Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )


which seems to be applicable to your case.



Vanderghast, Access MVP

 

[Jerry Whittle]

TheAnniversary: Int(DateDiff("yyyy",[Anniversary],Now()))