How to subtract milliseconds (in text format) in a query?

[neelsfer]

I have a timing application. Now my clients want milliseconds accuracy added to it.
I have two fields called timestart and timefinish.
I manage to add milliseconds etc with a button from main form (RaceF) to subform (timesf) in the start field in a table (TimeT) using this:

timestart = Format(Now, "dd/MM/yyyy HH:nn:ss") & "." & Right(Format(Timer, "#0.00"), 2)

and when they finish:

timefinish= Format(Now, "dd/MM/yyyy HH:nn:ss") & "." & Right(Format(Timer, "#0.00"), 2)

this is what format looks like:
starttime

27/02/2011 14:00:38.95

finishtime

27/02/2011 14:32:05.51

In a query, how do i subtract these start and finishtimes, getting a result in this format HH:nn:ss:ms or 00:00:00:00
the final result will never be more than 12:00:00:00 (12hrs)
I have added a mini example of my application here.
please help i am stuck

[ADezii]

I see no easy way to accomplish this since both Time Values are TEXT, and contain a Millisecond Component. I'm assuming that all digits past the Period (.), 51, 92, 78, etc. represent Milliseconds, namely: 51 Milliseconds, 92 Milliseconds, and 78 Milliseconds respectively. Is this assumption correct?

[neelsfer]

Hi Mr Adezi. Yes i assume it is that. Can one not add it to a "date field" perhaps? I have tried with no success.
I came across this code on the internet. It was from a VB 6 website, and it somehow gives me a millisecond value - is required for a specific type of race in my application that is currently only in seconds. I just need to subtract the start and finish times in this instance.
Any other suggestions on how to add milliseconds to a "datefield" in access.

[neelsfer]

I have just taken out the date

timestart = Format(Time, "HH:nn:ss") & "." & Right(Format(Timer, "#0.00"), 2)

then i get this ie as starting time

17:42:11.96

[neelsfer]

i got this code to generate ms but how to i link it to a button in form to create time in table

#
Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
#
Private Type SYSTEMTIME
#
    wYear As Integer
#
    wMonth As Integer
#
    wDayOfWeek As Integer
#
    wDay As Integer
#
    wHour As Integer
#
    wMinute As Integer
#
    wSecond As Integer
#
    wMilliseconds As Integer
#
End Type
#
#
#
'usage:
#
Dim MyTime As SYSTEMTIME
#
   GetLocalTime MyTime
 
Dim TimeText as String     
#
   TimeText = MyTime.wHour & ":" & MyTime.wMinute & ":" & MyTime.wSecond & "." & MyTime.wMilliseconds
 
   MsgBox TimeText 

[ADezii]

I got this code to generate ms but how to i link it to a button in form to create time in table

See the Revised Attachment, I think it says it all.

[neelsfer]

Thx Mr Adezi; It seems fine to me. Go for it pls

[ADezii]

@neelsfer:

Go for it pls

Not sure what you mean, kindly explain...

[neelsfer]

Sorry, i mean it looks 100% so far, as it adds MS.
Have you got an idea how one will subtract the 2 values?
Thx for your time. Appreciated

[ADezii]

I have an idea but I need to know exactly how you wish to display the Results, namely:

• 1 min./25 secs./345 millisecs.
• 2 hrs./14 mins./5 secs./13 millisecs.
• 21.87 mins.
• 00:00:23:234 (hh:mm:nn:ii)
• etc.

[neelsfer]

The best way for me is 00:00:23:234 (hh:mm:nn:ii)
thx

[ADezii]

@neelsfer - This will be fairly complicated, so I'll definitely need a little Time. The complexity, at least the way I see it, is having to subtract two TEXT Values which include Milliseconds, representing DATE/TIME Values, then return the Results in a precise Textual Format (hh:nn:ss:ii). Be patient, unless someone comes along with a better idea.

It is a couple of hours later, and I do believe that I have a workable solution. Click on the Timing Query Command Button and view the Results. I'll leave the Testing Phase up to you. See the Attached:

[neelsfer]

thx mr Adezi. I will test it. i appreciate your time and effort

[neelsfer]

i have started using the code in my application - works great!! Can't thank you enough Mr Adezi. Just have to add it to my reports, and then its finish!!.

[ADezii]

Glad it all worked out for you, neelsfer.