DateDiff Query (1 Viewer)

[StephenD]

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!!

 

[Rich]

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?

 

[StephenD]

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?

 

[Rich]

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).]

 

[StephenD]

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!!

 

[Rich]

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

 

[StephenD]

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!