how can I sum stop watch time

[msamir12]

Hi all ..
I have a stopwatch form in Access that records results in a table and I wants to sum the time in this table as it is 00:00:00:00

Thanks inadvance

[Rabbit]

I don't understand your question. If the time is always 0, then 0 + 0 = 0. There's no point in adding it up.

[msamir12]

I wanted to upload the form here and I already archived it into a rar file but it gave me error when uploading ...
about the time it is hh:mm:ss:ms

I start time then when I press reset button the time is recorded automatically into the table I want to "sum" this time

[msamir12]

the time is not alwas 0 this is the time format 00:00:00:00 that I want to sum

[Rabbit]

First of all, Access doesn't have a data type that is purely time. It always has a date component. The display format might only display the time but that doesn't mean the date part of the field isn't there, it's just not showing it.

Second, it doesn't make sense to add date time values. What would the result of Christmas + New Years = ? Really, what you want to add is a date time interval. And you have to decide what interval that is.

To get the interval from a date time data type, you can use the DateDiff() function. So if I wanted to calculate the number of minutes, I could do DATEDIFF(mm, 0, TimerField). Once I have converted the date time field into a time interval, then I can use the SUM() on it.

[msamir12]

Is there any way to send you the file ??

[msamir12]

here is a link for the file
http://www.4shared.com/rar/SSlhWa7Yba/stopwatch3.html

[Rabbit]

Sorry, I don't download files from people I don't know.

[zmbd]

Personally, I would look at storing your time as elapsed milliseconds. This would be a simple integer data type.

You can then convert the number of seconds to HH:MM:ss:ms

[msamir12]

so rabbit I sent you the access file to check it .. or tell me the way that you can help me with

[msamir12]

zmbd .. did you check the file ??
it is almost the same idea but I cannot sum that time or convert I need help with that as I am a little bit new to access DB and functions... thanks alot for response

[zmbd]

Provided the results are in a table, you should be able to create an aggregate query, as shown here Sum data by using a query once you have the sum, you can then format as needed.

You can also do the same within VBA; however, a query would be faster.


msamir12: Please understand, many of us cannot or will not d/l un-requested attachments. In my case, such d/l are prohibited by my ITSec staff. For many others, it is part of "safe computing/best practices" not to d/l such files. A practical example as to why can be found here http://bytes.com/topic/access/answer...l-ms-products. this unfourtunate Member opened a file from a trusted source and is now haveing all sorts of issues!

[msamir12]

Thanks zmbd I appretiate it and evaluate your efforts now I understand it very well I just wanted some one to check the case from the source file to give openion depending on case study that is it .. thanks once again for your help so as "Rabbit" as well ..
thank you guys

[NeoPa]

Let me just clarify. I suspect the actual format required is hh:mm:ss.ms. hh:mm:ss:ms makes little sense to me and I've never seen fractions of a second shown that way.

That said, always remember that what is shown (IE. The format.) is only relevant to data being viewed and any format should only ever be applied as the very last thing before displaying it. Data should always be worked on in its native format.

Although Rabbit is correct when he states that adding date/time values together produces results that are meaningless, this is only true for timestamp values. IE. Those representing a specific point in time. Subtracting one timestamp from another can make sense and results in a duration. Durations can sensibly be summed together and I suspect this is what you're talking about here. A duration is also a time (period) and can be stored as a Date/Time just as a timestamp value can. Bear in mind though, that the formatting in Office only really handles timestamps fully. This should only ever be a problem when dealing with durations, or sums of durations, that exceed twenty-four hours though. At least that is true unless any unit greater than hours is involved in the format, such as d, mm, yyyy, etc.

So, assuming for now that this won't be the case, you can store your durations in a Date/Time field. Be careful to avoid confusing these values with timestamp values of course.

Summing these values together is just as straightforward as summing values of numeric types together.

[hvsummer]

@msamir12:

One way that you allow you to sum those data, that is split hh:mm:ss.ms in each field, hh in integer field, mm in integer field, etc...

then set the sum condition, like this:
mm > 60 -> hh = hh + int(mm/60)
ss > 60 -> mm = mm + int(ss/60)
ms > 100 -> ss = ss + int(ms/100)

[msamir12]

I need a fast solution as I am new in here and in using access

[msamir12]

@hvsummer I think this is the nearest to what I need but I already have the stopwatch running like that and I already saved the data into a table but the thing here is how to sum this data as it is like that
00:00:00:57
00:00:10:46
this is a live data from the table I think the problem is in milliseconds if any one can help me with a code or to explain the way to do this I will be very Grateful to him thank you guys

[zmbd]

msamir12:
Please post the script you are using to run the stop-watch, this will tell us more about your data.

Please format the script with the [CODE/] when you post it.

[msamir12]

This is in the start/stop button :

Private Sub btnStartStop_Click()
 
 If Me.TimerInterval = 0 Then
      StartTickCount = GetTickCount()
      Me.TimerInterval = 15
      Me!btnStartStop.Caption = "Stop"
      Me!btnReset.Enabled = False
   Else
      TotalElapsedMilliSec = TotalElapsedMilliSec + _
         (GetTickCount() - StartTickCount)
      Me.TimerInterval = 0
      Me!btnStartStop.Caption = "Start"
      Me!btnReset.Enabled = True
   End If
 
End Sub

And this code is in the reset button :

  DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , , acNewRec
 
TotalElapsedMilliSec = 0
   Me!ElapsedTime = "00:00:00:00"
 

The OnOpen form code :

Private Sub Form_Open(Cancel As Integer)
 
TotalElapsedMilliSec = 0
 
End Sub

The on timer code :

Private Sub Form_Timer()
 
   Dim Hours As String
   Dim Minutes As String
   Dim Seconds As String
   Dim MilliSec As String
   Dim Msg As String
   Dim ElapsedMilliSec As Long
 
   ElapsedMilliSec = (GetTickCount() - StartTickCount) + _
      TotalElapsedMilliSec
 
   Hours = Format((ElapsedMilliSec \ 3600000), "00")
   Minutes = Format((ElapsedMilliSec \ 60000) Mod 60, "00")
   Seconds = Format((ElapsedMilliSec \ 1000) Mod 60, "00")
   MilliSec = Format((ElapsedMilliSec Mod 1000) \ 10, "00")
 
   Me!ElapsedTime = Hours & ":" & Minutes & ":" & Seconds & ":" _
      & MilliSec
 
End Sub

Also I have a problem that the time is recorded in a text field, not date/time field, in the table.

[hvsummer]

@msamir12:

Me!ElapsedTime = Hours & ":" & Minutes & ":" & Seconds & ":" _
      & MilliSec

this part of code join all 4 field into 1 (Me!ElapsedTime)
if you change that part of code into this

Me!Hours = Hours
Me!Minutes = minutes
Me!Seconds = seconds
Me!millisec = millisec

or

public function getTimeData(Type as integer)
TempArray = split(Me!ElapsedTime, ":")
getTimeData = TempArray(Type)
end function
 

then you can sum those field like I said in my previous comment.
(with priority ms -> sec -> minutes -> hours)
ofc you have to use vba to sum (make an function or sub).
we can't sum easily with total query (some what more complex than use vba)
but if you interesting in that, this example can help you

SELECT 
(Hours + iif(minutes > 60, int(minutes/60), 0) 
+ iif(minutes + int(seconds/60) > 60, int((minutes + int(seconds/60))/60), 0) 
+ iif(minutes + int(seconds/60) + int(int(ms/100)/60) > 60, int((minutes + int(seconds/60) + int(int(ms/100)/60))/60), 0)) as Hours,
 
iif((minutes + iif(seconds > 60, int(seconds/60), 0)
+iif(seconds + int(ms/100) > 60, int( (seconds + int(ms/100))/60), 0)) > 60 ,int( ((minutes + iif(seconds > 60, int(seconds/60), 0)
+iif(seconds + int(ms/100) > 60, int( (seconds + int(ms/100))/60), 0)))/60) ,(minutes + iif(seconds > 60, int(seconds/60), 0)
+iif(seconds + int(ms/100) > 60, int( (seconds + int(ms/100))/60), 0))) as minutes,
 
iif((seconds + iif(ms > 100, int(ms/100), 0)> 60, int( ((seconds + iif(ms > 100, int(ms/100), 0))/60), (seconds + iif(ms > 100, int(ms/100), 0)) as seconds,
 
iif(ms > 100, int(ms/100), ms) as ms
 
from YourTable
 

[NeoPa]

msamir12:
Also I have a problem that the time is recorded in a text field, not date/time field, in the table.

I would say this is your major problem. Sure, it's possible to do this using separate numbers, but I really wouldn't recommend that approach as it just makes everything so much more complicated. I suggest you fix that first then the rest should be fairly basic to do. If the value is stored as a basic Date/Time I mean of course. Separating the different elements instead of allowing the system to handle that for you is a recipe for more complicated work and much more probability of error.

[hvsummer]

@Neopa as you said lets system handle that, I was thinking why he has to save everything in that time format ?
hh:mm:ss.ms ?

@msamir12
you can convert those data into sec (can be 100 sec, 2000 sec, or 100000 sec)

access store time as part of day, so you can convert into sec by multiply 86400

then you will be able to sum those sec (something like sum([ElapsedTime] * 86400)), and convert data back into hh:mm:ss.ms ? all problem will vanish ==

[zmbd]

my thoughts
+ Declare {TotalElapsedMilliSec} as a form level variable of data-type long

+ In the current table you are strong your times, create a field called [elapsedtime] numeric(long)

+ [elapsedtime] will be the field to start storing the {TotalElapsedMilliSec} value in to the record set.
> Why not a date/time data-type, because the only time you are interested in the HH:MM:SS.ms format appears to be for display. A simple, primitive, data-type eloquently handles the raw data; thus, avoiding some of the "gotchas" of the date/time data-type and you can convert to the textual-human format "on the fly."

+ Take the code that converts {TotalElapsedMilliSec} in to the "HH:MM:SS.ms" convert this to a function. It will be used to format the time for displaying in your queries, forms, and reports. At no time will this format be saved. For example to show the formatted time in the control named "ElapsedTime"
Me!ElapsedTime = fHHMMSSms({TotalElapsedMilliSec})

In a query you could feed the [elapsedtime] to the function, in the GUI-QueryEditor:
HH_MM_SS_ms: fncHHMMSSms([elapsedtime])

Same thought for a calculated control etc...

+ With this arrangement you could then build your aggregate-query against your criteria and summing the [ElapsedTime] field, feeding the results of [Sum_of_ElapsedTime] to the fncHHMMSSms() function if needed for the displayed format.

I suggest starting a new thread if you need in-depth help with the following, you can include a link back to this thread if you need context :)

What to do with the current data?:
The following could be done via SQL and an update_query... I think; however, in this case I also think this to be very messy.

What I would do instead is, in VBA, open a DAO-Recordset against your table.
Move to the first record.
Create code that uses the split() function against the colon to split your data in to the 0-base-array feeding the function the field name you are storing your data.
So long as all of your data is "HH : MM : SS : MS"
then Array(0) with contain your Hours, Array(1)= minutes, etc; however, the values will be string-typecast because your field is text-typecast.

We need to convert the strings to integer ( cint(Array(n)) ) before we start our math (where n=your array element).

Reversing the computation wont be too hard. For example, converting hours to milliseconds:
accumulator = accumulator + (cint(array(0))*3.6e+6)

just work thru the array(n) using the correct conversion and add that to the total accumulator.

Once you have the accumulated total, store that value in [elapsedtime] for the record, move next record, rinse-repeat until end of record-set is found.

[NeoPa]

hvsummer:
@Neopa as you said lets system handle that, I was thinking why he has to save everything in that time format ?
hh:mm:ss.ms ?

I believe he's said that he's holding the data in string format. I believe it makes most sense to store the data as a Date/Time. It just makes everything so much easier. Certainly you can convert it to and from various integer formats, and hold them either separately or together, but I just don't see the benefit when you have a storage format that suits the situation perfectly.

This all just seems to me to be overcomplicating matters. It should still work mind, and whatever works for people is fine. If they find they can understand a complicated situation more easily when doing it a particular way then that shouldn't be discounted. After all, my preferences are mine. I would no more expect others to take those on than I'd be prepared to take others' preferences on myself.