449,314 Members | 1,758 Online
Need help? Post your question and get tips & solutions from a community of 449,314 IT Pros & Developers. It's quick & easy.

# Date difference

 P: 6 hi, I would like to calculate the date difference between two dates using field names which I have in my table. The field name is " Account opened on" for eg If the "Account open on" is Jan 5, 2007 the I would like to calculate the date difference between this date and the end of the quarter (March 31 2007) I would like Acess to calculate this difference automatically without me entering the end of the quarter date. please help thanks Sep 30 '07 #1
7 Replies

 Expert 2.5K+ P: 2,653 Hi, there. Do you mean difference between two dates stored in your table or between a date stored in your table and end of the quarter the date belongs to? Sep 30 '07 #2

 P: 6 The date "Account opened on" is stored in my table and I have to calculate the date difference between it and the quarter it belongs to (this is not stored in my table) thanks Sep 30 '07 #3

 Expert 2.5K+ P: 2,653 The date "Account opened on" is stored in my table and I have to calculate the date difference between it and the quarter it belongs to (this is not stored in my table) thanks Well. The following function will return end of the quarter the date belongs to Expand|Select|Wrap|Line Numbers Public Function GetEndOfTheQuarter(dteInput As Date) As Date       Dim intMonth As Integer       intMonth = Int((Month(dteInput) - 1) / 3) * 3 + 4     GetEndOfTheQuarter = DateAdd("d", -1, DateSerial(Year(dteInput), intMonth, 1))   End Function   the same as expression in query Expand|Select|Wrap|Line Numbers SELECT DateAdd("d", -1, DateSerial(Year([dteYourDate]), Int((Month([dteYourDate]) - 1) / 3) * 3 + 4, 1)) AS dteEndOfTheQuarter FROM tblYourTable;   Use DateDiff function to calculate difference between dates. Sep 30 '07 #4

 P: 6 Thank you very much. You saved me Sep 30 '07 #5

 Expert 2.5K+ P: 2,653 You are welcome. Best regards, Fish Sep 30 '07 #6

 Expert 2.5K+ P: 3,532 Lovely piece of logic, FishVal! Linq ;0)> Oct 1 '07 #7

 Expert 2.5K+ P: 2,653 Thanks, Linq. Oct 1 '07 #8