By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,314 Members | 1,758 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
7 Replies


FishVal
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

FishVal
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
  1. Public Function GetEndOfTheQuarter(dteInput As Date) As Date
  2.  
  3.     Dim intMonth As Integer
  4.  
  5.     intMonth = Int((Month(dteInput) - 1) / 3) * 3 + 4
  6.     GetEndOfTheQuarter = DateAdd("d", -1, DateSerial(Year(dteInput), intMonth, 1))
  7.  
  8. End Function
  9.  
the same as expression in query
Expand|Select|Wrap|Line Numbers
  1. SELECT DateAdd("d", -1, DateSerial(Year([dteYourDate]), Int((Month([dteYourDate]) - 1) / 3) * 3 + 4, 1)) AS dteEndOfTheQuarter FROM tblYourTable;
  2.  
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

FishVal
Expert 2.5K+
P: 2,653
You are welcome.

Best regards,
Fish
Sep 30 '07 #6

missinglinq
Expert 2.5K+
P: 3,532
Lovely piece of logic, FishVal!

Linq ;0)>
Oct 1 '07 #7

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

Post your reply

Sign in to post your reply or Sign up for a free account.