Can Time fields be added to show total?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two time fields a STime and ETime. I need them to add together to
show a total number of hrs in a separate field. Can this be done?
 
Lisa, you don't want to store a calculated value in a table, because
(1) it's unneccesary ( all the info you need is contained in the other two
fields, and
(2) there is a chance that the underlying data and the calculation may get
'out of sync

so, instead, make a query based on your table and create a field in the
query that looks like this:
(I'm assuming that your 'time' fields are actually numerical fields which
contain a number of hours, no?)

Totaltime: STime + ETime (type it just like that into the query builder)

or, better:

TotalTime: nz(STime,0) + nz(ETime,0)

(this will make the values add az zeros if there is nothing in the field,
otherwise the answer would be 'null' if one of the values was empty)

Now use your query where you previously used your table.
Hope this Helps
-John
 
You can create a column in a query with an expression like:
TotalHrs: DateDiff("h",STime, ETime)
or if you need greater accuracy:
TotalHrs: DateDiff("n",STime, ETime)/60
 
I have two time fields a STime and ETime. I need them to add together to
show a total number of hrs in a separate field. Can this be done?
Click to expand...

Sure.... in a query, on a form or in a report... NOT in a table.
Storing calculated data in a table is not a desirable thing in Access.
Store the components (STime and ETime). Then use

ElapsedTime:DateDiff("h",[STime],[ETime])

in a Query.
In a report or form you would use an unbound control:

=DateDiff("h",[STime],[ETime])
 
Lisa said:
I have two time fields a STime and ETime. I need them to add together to
show a total number of hrs in a separate field. Can this be done?
Click to expand...

Lisa,

If your start and end times cross between days, you will need
something like the VBA function below.


Public Function ElapsedTimeBetweenHours(StartDate As Date, EndDate
As Date) As Long

' Created: 12/28/2005
'
' Function to determine the elapsed time between two given times.
'
' StartDate must be less than EndDate.
'
' When this function returns "-1", StartDate was less than
' EndDate, and an error has occured.
'
' Returns: Long: Positive integers indicate hours, a -1 indicates
' an error.
'

Dim SecondsElapsed As Long
Dim OutTime as Long

SecondsElapsed = DateDiff("s", StartDate, EndDate)

If SecondsElapsed < 1 Then
OutTime = -1
Else
OutTime = SecondsElapsed \ 3600
End If

ElapsedTimeBetweenHours = OutTime

End Function


Sincerely,

Chris O.
 
Lisa

Access doesn't have a "time" data type field. What data type are you using
in your "time fields"?
 
Tim Ferguson said:
OutTime = Int((EndDate - StartDate) * 24)




Just a thought


Tim F
Click to expand...

Tim F,

That function was actually a shorted version of:

Public Function ElapsedTimeBetween(StartDate As Date, EndDate As
Date) As String

' Created: 12/28/2005
'
' Function to determine the elapsed time, in hours, minutes,
' and seconds, between two given times.
'
' StartDate must be less than EndDate.
'
' When this function returns "-1", StartDate was less than
' EndDate, and an error has occured.
'

Dim SecondsElapsed As Long
Dim HoursFromSeconds As Long
Dim MinutesFromSeconds As Long
Dim SecondsRemaining As Long
Dim OutTime as String

SecondsElapsed = DateDiff("s", StartDate, EndDate)

If SecondsElapsed < 1 Then
OutTime = "-1"
Else
HoursFromSeconds = SecondsElapsed \ 3600
MinutesFromSeconds = (SecondsElapsed Mod 3600) \ 60
SecondsRemaining = (SecondsElapsed Mod 3600) Mod 60

OutTime = Format(HoursFromSeconds, "00") & ":" & _
Format(MinutesFromSeconds, "00") & ":" & _
Format(SecondsRemaining, "00")
End If

ElapsedTimeBetween = OutTime

End Function

With some of the lines cut out.

And yes, there are other ways of writing out the calculations.


Sincerely,

Chris O.
 
Back
Top