If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating Time Values
I'm trying to calculate the difference between 2 "Date/Time" fields to
display in a form. My Control Source is listed as '=[EndTime]-[StartTime]'. This works accurately only if calculating THE SAME DAY. For e.g. if I start on Mon. & end on Tues. it displays as if its THE SAME DAY (a Mon. end-time brings the same values as a Tue. end-time.) (I have my field - 'txtTotalTime' - w/ a Format setting to "Short Time") Thanks in advance for your response, Michael P.S. the existing posts/comments from others didn't seem to work... |
#2
|
|||
|
|||
Calculating Time Values
Take a look at Access HELP for the syntax related to the DateDiff()
function. Regards Jeff Boyce Microsoft Access MVP "Michaelcip" wrote in message ... I'm trying to calculate the difference between 2 "Date/Time" fields to display in a form. My Control Source is listed as '=[EndTime]-[StartTime]'. This works accurately only if calculating THE SAME DAY. For e.g. if I start on Mon. & end on Tues. it displays as if its THE SAME DAY (a Mon. end-time brings the same values as a Tue. end-time.) (I have my field - 'txtTotalTime' - w/ a Format setting to "Short Time") Thanks in advance for your response, Michael P.S. the existing posts/comments from others didn't seem to work... |
#3
|
|||
|
|||
Calculating Time Values
On Fri, 2 Oct 2009 13:46:02 -0700, Michaelcip wrote:
I'm trying to calculate the difference between 2 "Date/Time" fields to display in a form. My Control Source is listed as '=[EndTime]-[StartTime]'. This works accurately only if calculating THE SAME DAY. For e.g. if I start on Mon. & end on Tues. it displays as if its THE SAME DAY (a Mon. end-time brings the same values as a Tue. end-time.) (I have my field - 'txtTotalTime' - w/ a Format setting to "Short Time") Thanks in advance for your response, Michael P.S. the existing posts/comments from others didn't seem to work... Use the DateDiff() function and include the date as well as the time, otherwise how would Access (or anybody) know that the time extends over to the next day? Note also that the result is a number value, (x hours), not a time value (x O'Clock), so setting the value to a Time format would NOT be appropriate. Also, this value ought not be saved in any table. Anytime you need the value, calculate it, as below. The expression would be (in an UNBOUND control): =DateDiff("h",StartTime,EndTime) = DateDiff("h",#10/1/2009 11:00 PM#,#10/2/2009 3:00 AM#) returns 4 (hours) The above returns a value in elapsed hours. If you need to calculate, for example, the elapsed time between #10/1/2009 11:15 PM# and #10/2/2009 3:25 AM#, then calculate the time in minutes and divide by 60: =DateDiff("n",#10/1/2009 11:15 PM#,#10/2/2009 3:25 AM#)/60 returns 4.166 (hours) Look up the DateDiff function in VBA help. If you would like the elapsed time to compute as 4 hours, 10 minutes then see: http://www.accessmvp.com/djsteele/Diff2Dates.html -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
Thread Tools | |
Display Modes | |
|
|