Hi everyone,

I'm using the datediff function to calulate the number of months between two dates. I think I've over-estimated the power of Microsoft Access!..........
I got two date fields in the format dd/mm/yy.
I'm using the formuala: Datediff("m",[Date1],[Date2})
I thought this was working fine but instead of calculating the number of actual months it only calculates the number of times the month changes!
So if you have a dates of 27/12/01 and 02/01/02, it returns 1 as the number of months between the two dates.
But if you have 01/12/01 and 27/12/01 it returns 0 as the number of months between the two months!
How useless is that!
It doesn't seem to warn you of this in the help file-I'm getting pretty tired of Access now and all the assumed knowledge you are supposed to have!!!
Can anyone help me out there before I chuck my PC out of the window!!

Not sure exactly what your trying to achieve but isn't the difference between 01/12/01 and 27/12/01 less than a calendar month? What value would you expect to see in this case?

Sorry-yes is it correct for the two dates 01/12/01 and 27/12/01.
I'm concerned about it putting 1 when the difference is only say a few days but the days go across two different months. As in my first example of 27/12/01 and 02/01/02.
Does this make sense?

Function GetMonths(dteA As Date, dteB As Date)
If Day(dteA) < Day(dteB) Then
GetMonths = DateDiff("m", dteA, dteB) - 1
Else
GetMonths = DateDiff("m", dteA, dteB)
End If
End Function


[This message has been edited by Rich (edited 03-12-2002).]

Thanks Rich,

What exactly is this doing?
How do you find stuff like this out?
The access help files on datediff seem to sell it as a pretty obvious function that will calculate the number of months, days, or whatever between two dates.
After using this in some pretty important analysis on our customers, I now have to go back to my boss and tell them that all the
analysis is wrong!! Nightmare!!

If you intend to carry on programming expect to spend the rest of your life learning. I don't remember now how I came to discover the Day function, probably combinations of frustration, trial and error http://www.access-programmers.co.uk/ubb/smile.gif

Thanks!,

Didn't think I was programming-simply tring to do a simple calculation of the difference between two dates using a built in function-but there you go!