DateDiff for Months

[Delta729]

I could use some help, because I am not finding a good way to do this. I have a table with [Contract Start Date] and [Contract End Date]. In my query, I have the following:



Contract Duration: DateDiff("m",[tblObjective]![Contract Start Date],[tblObjective]![Contract End Date]) & " Months"

The issue is that if a contract starts on 9/1/14 and ends on 12/31/14, it's showing 3 months. After reading several articles on Google, I guess I understand why. Is there an easy way for this to show as 4 months?

[Delta729]

I also need to say that I'm not a big user of Access, and really a complete novice if the answer if VB code. So, hopefully this isn't too difficult. Also, maybe Datediff is just the wrong thing to be using and maybe some function was designed for this that I couldn't find?

[ssanfu]

IF the contract end date will ALWAYS be the last day of the month, you can add 1 day to the contract end date.

Try this query

SELECT tblObjective.[Contract Start Date], tblObjective.[Contract End Date], DateDiff("m",tblObjective.[Contract Start Date],tblObjective.[Contract End Date]) & " Months" AS [Contract Duration], DateDiff("m",tblObjective.[Contract Start Date],tblObjective.[Contract End Date]+1) & " Months" AS [Contract Duration2]
FROM tblObjective;

[Delta729]

Thanks for the reply, unfortunately the contract end date can be any day of the month.

[ssanfu]

Please fill in what you say the number of months should be

Row	Start	End		Months
1	9/1/2014	12/1/2014	=
2	9/1/2014	12/10/2014	=
3	9/1/2014	12/20/2014	=
4	9/1/2014	12/31/2014	=