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

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]

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

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