If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DateDiff("yyyy",[Anniversary],Now()) returns rounded number
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. |
#2
|
|||
|
|||
DateDiff("yyyy",[Anniversary],Now()) returns rounded number
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 apex77 wrote: 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. |
#3
|
|||
|
|||
DateDiff("yyyy",[Anniversary],Now()) returns rounded number
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 "apex77" wrote in message ... 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. |
#4
|
|||
|
|||
DateDiff("yyyy",[Anniversary],Now()) returns rounded number
TheAnniversary: Int(DateDiff("yyyy",[Anniversary],Now()))
-- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "apex77" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|