By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,284 Members | 1,243 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,284 IT Pros & Developers. It's quick & easy.

calculating the interval between two times in an Access query

P: 56

I have two times held as 'Short Time' in a date/time field in Access. I tried to use a DateDiff function in a query to calcualte the interval between these two times but it just returns a #Error.

Can anyone tell me how I can calculate the difference between these two times?

Nov 21 '08 #1
Share this Question
Share on Google+
3 Replies

Expert 5K+
P: 8,677
To calculate the difference in Minutes between Time1 and Time2, and to also allow for the possibility of Time1 being prior to Midnight and Time2 post Midnight, and to place these values into a Calculated Field named [Time Diff]:
Expand|Select|Wrap|Line Numbers
  1. Time Diff: IIf(DateDiff("n",[Time1],[Time2])>0,DateDiff("n",[Time1],[Time2]),DateDiff("n",[Time1],[Time2])+1440)
Nov 21 '08 #2

P: 56
That works a treat - thanks very much. What is the +1440 doing?
Nov 24 '08 #3

Expert 5K+
P: 8,677
The +1440 figures in the pre-midnight/post-midnight differential and adjusts, for example:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print DateDiff("n", #23:00 PM#,#04:00 AM#)
  2. 'produces ==> -1140 (19 hours)
Adding the +1440 to -1140 produces ==> 300 (5 hours) as in:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print DateDiff("n", #23:00 PM#,#04:00 AM#) + 1440 ==> 300
Nov 24 '08 #4

Post your reply

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