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

How to calculate the difference between the values of two fields containing times

P: 33
Hi,
Using MS Access 97, i have a query over an As/400 which returns a number of fields. Within each record returned, two fields are of numeric data type but contain values relating to time.
The first field contains the start time, the second field contains the finish time.

Within my query, I need to calculate the the difference bewteen the two in order to ascertain the duration.
Finish Time minus Start Time dosent work as the value isnt calculation hours/minutes.

Any advice
Mar 2 '09 #1
Share this Question
Share on Google+
4 Replies


100+
P: 675
Your table contains Start and End times as numeric and not date/time fields. How are you storing these times? What units are assumed (hours, minutes, etc.) for these fields? What would be typical values for these fields?
Mar 2 '09 #2

P: 33
Hi,
Im not actually storing them within a table, only capturing them from the as/400.
As you expected, ref the units, HHMMSS
Typical Values would be;

From Time of 181005
To Time of 192707

tks

@OldBirdman
Mar 2 '09 #3

100+
P: 675
I'm not familiar with as/400. I may have responded to something I can't do.
You're using Access, so either you will be running a query on your "incoming" data, or you will have fields on a form.
Either way, the Access function CDate("18:10:05") returns 6:10:05 PM, so the logic here might be:
Expand|Select|Wrap|Line Numbers
  1. Public Function TimeDiff(strStartTime As String, strEndTime As String) As String
  2. Dim dteEndTime As Date
  3. Dim dteStartTime As Date
  4. Dim lngElapsedMinutes As Long
  5.  
  6. dteEndTime = CDate(Mid(strEndTime, 1, 2) & ":" & Mid(strEndTime, 3, 2) & ":" & Mid(strEndTime, 5, 2))
  7. dteStartTime = CDate(Mid(strStartTime, 1, 2) & ":" & Mid(strStartTime, 3, 2) & ":" & Mid(strStartTime, 5, 2))
  8. lngElapsedMinutes = DateDiff("n", dteStartTime, dteEndTime)
  9. TimeDiff = lngElapsedMinutes
  10. End Function
Code might be needed to round seconds to minutes, or convert to an HHMMSS or HHMM format.
Mar 2 '09 #4

P: 33
Tks Birdman, much appreciated. Query works a treat.
Mar 2 '09 #5

Post your reply

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