Date difference problem

[bravo6]

I am using this formula in a query field to compute the number of months between two dates:
Months: Month(forms![Corporate Agent Productivity]!enddate-forms![Corporate Agent Productivity]!startdate)

If the enddate is 05/31/07 and the startdate is 04/01/07 it returns the expected "2" for the number of months.

But I've discovered that when you have two months in a row that contain 31days (JUL-AUG and DEC-JAN) it returns an extra month. example: If the enddate is 08/31/07 and the startdate is 07/01/07 the query returns "3" instead of "2"

Can anyone help with this problem?

 

[SkipVought]

Hi,

MONTH is an inexact unit, as you have discovered.

I'd perfer to calculate the difference between two dates directly and use my own KNOW criteria for determining MY definition of MONTH.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[genomon]

Or you can use datepart & datediff functions.....

Did you hear about the Buddhist who refused Novocain during a root
canal? He wanted to transcend dental medication.

 

[SkipVought]

interesting, I get ONE month's difference, not two or three.
[codee]
Dim d1 As Date, d2 As Date
d1 = #7/1/2007#
d2 = #8/31/2007#
MsgBox DateDiff("m", d1, d2)
[/code]
I incorrectly ASSUMED that you were using the DateDiff Function.

<Key information>
What FORMULA are you using?

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[bravo6]

I fixed the problem by doing the following.
Months: Month((DateAdd("d",-1,([forms]![Corporate Agent Productivity]![enddate])))-([forms]![Corporate Agent Productivity]![startdate]))

By taking one day from the end date, the system now sees 7/1/07-8/31/07 as 2 months instead of 3. I only had the problem if the date range was 12/1/xx-01/31/xx+1 or beteen 07/01-08/31. If I put in a date range of 07/01-08/30 it WAS seeing that as 2 months. So by taking away one day, the syetem is tricked. I guess the code must say that anything over 61 days is 3 months.