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 Problem
Folks,
Trying to undestand why DateDiff("m", 24/07/09,22/01/10) is producing a result of 6 when if you work it out, as we have not yet passed 24/01/10, it should really be a result of 5. Presume Datediff is right, and I have set it up wrong, but any help would be appreciated. I am using it to calculate in a form when rent is due, obviously rent (in this case) is paid monthly, and only once the "anniversary date" is passed, so this is causing me a few issues! Cheers, Russell. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201001/1 |
#2
|
|||
|
|||
DateDiff Problem
On Fri, 22 Jan 2010 17:04:38 GMT, "Pascoe via AccessMonster.com" u43716@uwe
wrote: Trying to undestand why DateDiff("m", 24/07/09,22/01/10) is producing a result of 6 when if you work it out, as we have not yet passed 24/01/10, it should really be a result of 5. Presume Datediff is right, and I have set it up wrong, but any help would be appreciated. I am using it to calculate in a form when rent is due, obviously rent (in this case) is paid monthly, and only once the "anniversary date" is passed, so this is causing me a few issues! DateDiff returns the number of *boundaries* crossed between the two arguments: ?datediff("m", #1/31/2010 23:59:59#, #2/1/2010 00:00:00#) 1 Only one second separates the two date values... but it crosses a month boundary. To calculate when rent is due, I'd suggest using the DateAdd() function instead: ?dateadd("m", 6, #7/24/09#) 1/24/2010 Do note how it handles short months though: ?dateadd("m", 7, #7/31/09#) 2/28/2010 You could use this function in conjunction with a handy little table Num with one field N, values 0 through 10000 or so to calculate a whole series of duedates. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
DateDiff Problem
DateDiff calculates the number of changes that have occurred, In your case,
the month changed from July to Aug (that's 1), Aug to Sept (that's 2), Sept to Oct (that's 3), Oct to Nov (that's 4), Nov to Dec (that's 5) and Dec to Jan (that's 6). Note that DateDiff("yyyy", #2009-12-31#, #2010-01-01#) will report a difference of one year, despite the fact that's there's only 1 day between the two dates because the year has changed. Use code like: DateDiff("m", Date1, Date2) - IIf(Format(Date2, "mmdd") Format(Date1, "mmdd"), 1, 0) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Pascoe via AccessMonster.com" u43716@uwe wrote in message news:a27ff2cf5c498@uwe... Folks, Trying to undestand why DateDiff("m", 24/07/09,22/01/10) is producing a result of 6 when if you work it out, as we have not yet passed 24/01/10, it should really be a result of 5. Presume Datediff is right, and I have set it up wrong, but any help would be appreciated. I am using it to calculate in a form when rent is due, obviously rent (in this case) is paid monthly, and only once the "anniversary date" is passed, so this is causing me a few issues! Cheers, Russell. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201001/1 |
#4
|
|||
|
|||
DateDiff Problem
Thank you both for your assistance. I am grateful for your insight, and will
re-jig accordingly. Kind Regards, Russell. Douglas J. Steele wrote: DateDiff calculates the number of changes that have occurred, In your case, the month changed from July to Aug (that's 1), Aug to Sept (that's 2), Sept to Oct (that's 3), Oct to Nov (that's 4), Nov to Dec (that's 5) and Dec to Jan (that's 6). Note that DateDiff("yyyy", #2009-12-31#, #2010-01-01#) will report a difference of one year, despite the fact that's there's only 1 day between the two dates because the year has changed. Use code like: DateDiff("m", Date1, Date2) - IIf(Format(Date2, "mmdd") Format(Date1, "mmdd"), 1, 0) Folks, [quoted text clipped - 13 lines] Cheers, Russell. -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|