Count Months Between to Dates

[JMarcus]

Count the number of Months Between two dates
Hello

Does anybody know a query expression that will enable me to count the number of months between two dates fields. For example [1/1/2015] to [11/2/2015] is 11 months



Thanks

 

[plog]

Are you sure its 11 months? I think 1/1/2015 to 11/2/2015 is 10 months. You are really going to have to try hard to make a definition that you can use if you want that to be 11 months.

DateDiff will subtract the month values from 2 dates: http://www.techonthenet.com/access/functions/date/datediff.php


Just beware, it returns 2 for 1/31/2016 to 3/1/2016. That may or may not be the answer you want.

 

[JMarcus]

It is two different fields though doesn't list it [Date 1] and [Date 2] or [1/1/2015] to [11/2/2015]

 

[arnelgp]

datediff("m",[date1],[date2])
if [date1] month is 1 and [date2] month is 11 and both have same year, the result would be 10.

datediff("m",[date1],[date2])+1
if [date1] month is 1 and [date2] month is 11 and both have same year, the result would be 11.

 

[plog]

It is two different fields though doesn't list it [Date 1] and [Date 2] or [1/1/2015] to [11/2/2015]

Huh? I do not know what you are trying to express with that.

 

[JMarcus]

Thanks looks logical. I keep getting an #Error though.