How to calculate the total time of a process in MS Access

[meLady]

Hello,

Here I am having a little bit confusion about how to calculate the total time of steps of a process (for example: registering a new user for a website) in MS Access:

- step1: entering a user name in a text box will take 00:00:10,94
- step2: entering a user password in a text box will take 00:00:03,12
- step3: entering a user confirmation password will take 00:01:30,16
- step4: entering a user email address in a text box will take 00:00:07,79

so, the total time of each step will be 00:00:52,01

I need a solution of it, because it is my first time to deal with this concept????

[ADezii]

Hello,

Here I am having a little bit confusion about how to calculate the total time of steps of a process (for example: registering a new user for a website) in MS Access:

- step1: entering a user name in a text box will take 00:00:10,94
- step2: entering a user password in a text box will take 00:00:03,12
- step3: entering a user confirmation password will take 00:01:30,16
- step4: entering a user email address in a text box will take 00:00:07,79

so, the total time of each step will be 00:00:52,01

I need a solution of it, because it is my first time to deal with this concept????

The easiest Method for creating a Stopwatch for various processes is with the timeGetTime() API Function. This simple Function returns the number of milliseconds that have passed since Windows was started and is very accurate. Here is a code snippet that will illustrate its use:

'Declare in a Standard Code Module
Public Declare Function timeGetTime Lib "winmm.dll" () As Long

Basic Code Template:

Dim lngStartTime As Long, lngEndTime As Long, T As Long

lngStartTime = timeGetTime()

For T = 1 To 20000
  Debug.Print T / 125
Next

lngEndTime = timeGetTime()

Debug.Print "It took " & CStr(lngEndTime - lngStartTime) & " milliseconds to complete this process"
Debug.Print "It took " & CStr((lngEndTime - lngStartTime) / 1000) & " seconds to complete this process"

OUTPUT:

It took 5125 milliseconds to complete this process
It took 5.125 seconds to complete this process

[meLady]

Hello ADezii ,

Thanks for your help, and it was very interesting answer, but it wasn't what i am looking for! I really don't want to use a Stop Watch for the process. the idea is that I have two fields in a table named "Total Time".

the first field i named it "Process Type", where I type each step of a process as I mentioned above. And the second field I named it as "Timing", where I type the time manually for each step.

in a form, there will be a control source "text box" that will calculate or sum all time that are in the "Timing" field which are listed manually by me.

The real thing to say that I am not very good at time calculation specially in MS Access that why I need some solution of my confusion. And your answer was very great but it did not suit with my project requirements.

And I am very sorry for not clearing my point at the first post. So, I have cleared my point which is better than before.

[meLady]

any reply to my question!

[ADezii]

any reply to my question!

meLady, sorry for the delay. Kindly clarify 1 thing for me before I attempt a solution for your dilemma. It concerns your Format 00:01:30,16. Does this mean 0 Hours, 1 Minute, 30.16 Seconds.If this is the case, the Total Time would be 1 Minute, 52.01 Seconds - not 52.01 Seconds. I must know this before I can proceed.

[meLady]

Hello ADezii,

Thanks for your concern in tracking my queries,which really makes me feel very happy ... about your question ...OF COURSE ... Your calculation is perfect (^_^) ...

Yes the answer of calculation must and must be (1) minute, 52.01 seconds according to the recorded time 00:01:30,16 ... which I miscalculated it!

I am very sorry for providing you the wrong answer ... again sorry ... (^_^) I am waiting for your kindly reply.

[meLady]

Hello ADezii

[ADezii]

Hello ADezii

meLady:
I am very pressed for time but here is the basic logic without accounting for overages as in:

1. Hundreths of a Second greater than 99 would round up to another Second.
2. Seconds greater than 59 would round up to another minute.
3. Minutes greater than 59 would round up to another Hour.
4. If you need further help, let me know but I cannot guarantee a quick response.

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim intHundrethsOfSeconds As Integer, intNoOfSeconds
Dim intNoOfMinutes As Integer, intNoOfHours As Integer

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Total Time", dbOpenDynaset)

MyRS.MoveFirst

Do While Not MyRS.EOF
  intNoOfHours = intNoOfHours + Val(Left(MyRS![Timing], 2))
  intNoOfMinutes = intNoOfMinutes + Val(Mid(MyRS![Timing], 4, 2))
  intNoOfSeconds = intNoOfSeconds + Val(Mid(MyRS![Timing], 7, 2))
  intHundrethsOfSeconds = intHundrethsOfSeconds + Val(Right(MyRS![Timing], 2))
    MyRS.MoveNext
Loop

MyRS.Close

Debug.Print Format(intNoOfHours, "00") & ":" & Format(intNoOfMinutes, "00") & ":" & _
            Format(intNoOfSeconds, "00") & "," & Format(intHundrethsOfSeconds, "00")

OUTPUT:

00:01:50,201 which is equivqlent to 00:01:52,01

NOTE: The code assumes a consistent Format of HH:MM:SS,hh

[meLady]

Hello ADEZII,

How are you my friend? Thanks for the coding ... Your answers are always amazing ... Sorry for making troubles ... Thanks alot for your kindly concern my friend .... Just Keep it up with us in MS ACCESS Group (^_^)

[ADezii]

Hello ADEZII,

How are you my friend? Thanks for the coding ... Your answers are always amazing ... Sorry for making troubles ... Thanks alot for your kindly concern my friend .... Just Keep it up with us in MS ACCESS Group (^_^)

No trouble at all, meLady. There is 1 thing that I forgot - filtering for the Process Type. I'll adjust the code for that then whenever I get the chance, I'll factor in the overages later.

[ADezii]

Hello,

Here I am having a little bit confusion about how to calculate the total time of steps of a process (for example: registering a new user for a website) in MS Access:

- step1: entering a user name in a text box will take 00:00:10,94
- step2: entering a user password in a text box will take 00:00:03,12
- step3: entering a user confirmation password will take 00:01:30,16
- step4: entering a user email address in a text box will take 00:00:07,79

so, the total time of each step will be 00:00:52,01

I need a solution of it, because it is my first time to deal with this concept????

Here are the code adjustments (Bold) for factoring in the Process Type:

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim intHundrethsOfSeconds As Integer, intNoOfSeconds
Dim intNoOfMinutes As Integer, intNoOfHours As Integer
[B]Dim MySQL As String[/B]

[B]MySQL = "Select * From [Total Time] Where [Process Type]='Active'"[/B]

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset([B]MySQL[/B], dbOpenDynaset)

MyRS.MoveFirst

Do While Not MyRS.EOF
  intNoOfHours = intNoOfHours + Val(Left(MyRS![Timing], 2))
  intNoOfMinutes = intNoOfMinutes + Val(Mid(MyRS![Timing], 4, 2))
  intNoOfSeconds = intNoOfSeconds + Val(Mid(MyRS![Timing], 7, 2))
  intHundrethsOfSeconds = intHundrethsOfSeconds + Val(Right(MyRS![Timing], 2))
    MyRS.MoveNext
Loop

MyRS.Close

Debug.Print Format(intNoOfHours, "00") & ":" & Format(intNoOfMinutes, "00") & ":" & _
            Format(intNoOfSeconds, "00") & "," & Format(intHundrethsOfSeconds, "00")

[ADezii]

Hello,

Here I am having a little bit confusion about how to calculate the total time of steps of a process (for example: registering a new user for a website) in MS Access:

- step1: entering a user name in a text box will take 00:00:10,94
- step2: entering a user password in a text box will take 00:00:03,12
- step3: entering a user confirmation password will take 00:01:30,16
- step4: entering a user email address in a text box will take 00:00:07,79

so, the total time of each step will be 00:00:52,01

I need a solution of it, because it is my first time to deal with this concept????

Here is the finalized code that should produce exactly what you had requested. As long as you maintain the exact HH:MM:SS,hh specification, Table, and Field Names, as you originally indicated, all should be OK. The code has been placed in a Public Function in order to provide you with the greatest flexibility. Simply call the Function and pass it a String indicating the [Process Type], the Function returns a properly formatted String representing your accumulated Total Times for a given Process Type:

Dim strSomeString As String
strSomeString = fCalculateTimeTotals("<your process type>")

[B]Public Function fCalculateTimeTotals(strProcessType As String) As String[/B]
Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim intHundrethsOfSeconds As Integer, intNoOfSeconds
Dim intNoOfMinutes As Integer, intNoOfHours As Integer
Dim MySQL As String

MySQL = "Select * From [Total Time] Where [Process Type]='" & strProcessType & "'"

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)

MyRS.MoveFirst

Do While Not MyRS.EOF
  intHundrethsOfSeconds = intHundrethsOfSeconds + Val(Right(MyRS![Timing], 2))
  intNoOfSeconds = intNoOfSeconds + Val(Mid(MyRS![Timing], 7, 2))
  intNoOfMinutes = intNoOfMinutes + Val(Mid(MyRS![Timing], 4, 2))
  intNoOfHours = intNoOfHours + Val(Left(MyRS![Timing], 2))
    MyRS.MoveNext
Loop

MyRS.Close

'Debug.Print Format(intNoOfHours, "00") & ":" & Format(intNoOfMinutes, "00") & ":" & _
            Format(intNoOfSeconds, "00") & "," & Format(intHundrethsOfSeconds, "00")

'Now, check for overages in each set (1st Hundreths)
If intHundrethsOfSeconds >= 100 Then
  intNoOfSeconds = intNoOfSeconds + Int(intHundrethsOfSeconds / 100)
  intHundrethsOfSeconds = (intHundrethsOfSeconds - Int(intHundrethsOfSeconds / 100) * 100)
End If

'Let's check Seconds now for overages
If intNoOfSeconds >= 60 Then
  intNoOfMinutes = intNoOfMinutes + Int(intNoOfSeconds / 60)
  intNoOfSeconds = (intNoOfSeconds - Int(intNoOfSeconds / 60) * 60)
End If

'Let's check Minutes now for overages
If intNoOfMinutes >= 60 Then
  intNoOfHours = intNoOfHours + Int(intNoOfMinutes / 60)
  intNoOfMinutes = (intNoOfMinutes - Int(intNoOfMinutes / 60) * 60)
End If

'Debug.Print Format(intNoOfHours, "00") & ":" & Format(intNoOfMinutes, "00") & ":" & _
            'Format(intNoOfSeconds, "00") & "," & Format(intHundrethsOfSeconds, "00")
'Debug.Print "---------------------"

fCalculateTimeTotals = Format(intNoOfHours, "00") & ":" & Format(intNoOfMinutes, "00") & ":" & _
                       Format(intNoOfSeconds, "00") & "," & Format(intHundrethsOfSeconds, "00")
[B]End Function[/B]

NOTE: Let me know how you make out.

[NishanG]

Can I get the file

Here is the finalized code that should produce exactly what you had requested. As long as you maintain the exact HH:MM:SS,hh specification, Table, and Field Names, as you originally indicated, all should be OK. The code has been placed in a Public Function in order to provide you with the greatest flexibility. Simply call the Function and pass it a String indicating the [Process Type], the Function returns a properly formatted String representing your accumulated Total Times for a given Process Type:

Dim strSomeString As String
strSomeString = fCalculateTimeTotals("<your process type>")

[B]Public Function fCalculateTimeTotals(strProcessType As String) As String[/B]
Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim intHundrethsOfSeconds As Integer, intNoOfSeconds
Dim intNoOfMinutes As Integer, intNoOfHours As Integer
Dim MySQL As String

MySQL = "Select * From [Total Time] Where [Process Type]='" & strProcessType & "'"

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)

MyRS.MoveFirst

Do While Not MyRS.EOF
  intHundrethsOfSeconds = intHundrethsOfSeconds + Val(Right(MyRS![Timing], 2))
  intNoOfSeconds = intNoOfSeconds + Val(Mid(MyRS![Timing], 7, 2))
  intNoOfMinutes = intNoOfMinutes + Val(Mid(MyRS![Timing], 4, 2))
  intNoOfHours = intNoOfHours + Val(Left(MyRS![Timing], 2))
    MyRS.MoveNext
Loop

MyRS.Close

'Debug.Print Format(intNoOfHours, "00") & ":" & Format(intNoOfMinutes, "00") & ":" & _
            Format(intNoOfSeconds, "00") & "," & Format(intHundrethsOfSeconds, "00")

'Now, check for overages in each set (1st Hundreths)
If intHundrethsOfSeconds >= 100 Then
  intNoOfSeconds = intNoOfSeconds + Int(intHundrethsOfSeconds / 100)
  intHundrethsOfSeconds = (intHundrethsOfSeconds - Int(intHundrethsOfSeconds / 100) * 100)
End If

'Let's check Seconds now for overages
If intNoOfSeconds >= 60 Then
  intNoOfMinutes = intNoOfMinutes + Int(intNoOfSeconds / 60)
  intNoOfSeconds = (intNoOfSeconds - Int(intNoOfSeconds / 60) * 60)
End If

'Let's check Minutes now for overages
If intNoOfMinutes >= 60 Then
  intNoOfHours = intNoOfHours + Int(intNoOfMinutes / 60)
  intNoOfMinutes = (intNoOfMinutes - Int(intNoOfMinutes / 60) * 60)
End If

'Debug.Print Format(intNoOfHours, "00") & ":" & Format(intNoOfMinutes, "00") & ":" & _
            'Format(intNoOfSeconds, "00") & "," & Format(intHundrethsOfSeconds, "00")
'Debug.Print "---------------------"

fCalculateTimeTotals = Format(intNoOfHours, "00") & ":" & Format(intNoOfMinutes, "00") & ":" & _
                       Format(intNoOfSeconds, "00") & "," & Format(intHundrethsOfSeconds, "00")
[B]End Function[/B]

NOTE: Let me know how you make out.

Can I get the file which this was created

[SioSio]

https://docs.microsoft.com/en-us/windows/win32/sysinfo/acquiring-high-resolution-time-stamps?redirectedfrom=MSDN

With reference to the site mentioned above, I made a sample code to measure the processing time with high accuracy of milliseconds or less.
This sample code measures the time between when you start typing in TextBox1 and when you press CommandButton1.

Option Explicit 
Private Declare PtrSafe Function QueryPerformanceCounter Lib "Kernel32" (X As Currency) As Boolean
Private Declare PtrSafe Function QueryPerformanceFrequency Lib "Kernel32" (X As Currency) As Boolean
Dim Freq As Currency
Dim Overhead As Currency
Dim C1 As Currency, C2 As Currency, ms As Currency
Dim StopWatch As Boolean
Dim h As Currency, m As Currency, s As Currency
Dim hms As String
 
'the processing time with high accuracy of milliseconds
Public Sub StopWatchStart()
    If QueryPerformanceCounter(C1) Then
        QueryPerformanceCounter C2
        QueryPerformanceFrequency Freq
        Overhead = C2 - C1
    End If
    QueryPerformanceCounter C1
End Sub
 
Public Sub StopWatchStop()
    QueryPerformanceCounter C2
    ms = (C2 - C1 - Overhead) / Freq '[sec]
    h = Int(ms / 60 / 60)
    m = Int((ms - h * 60 * 60) / 60)
    s = ms - h * 60 * 60 - m * 60
    hms = Format(h, "00") & ":" & Format(m, "00") & ":" & Format(s, "00.00")
End Sub
 
Public Sub StopWatchShow(Optional Caption As String)
    MsgBox Caption & " " & hms
End Sub

Private Sub UserForm_Initialize()
    StopWatch = False
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If StopWatch = False Then
        StopWatch = True
        StopWatchStart
    End If
End Sub

Private Sub CommandButton1_Click()
    StopWatchStop
    StopWatch = False
    StopWatchShow "Time involved in input :"
End Sub

[isladogs]

I have a website article comparing the accuracy of 6 different methods of timing processes in Access. The forum doesn't allow me to provide a link but if you do a web search for "Timer Comparison Tests - Mendip Data Systems" you should easily find it.

The tests are:
1.Timer VBA – number of seconds since midnight but to millisecond resolution

2. GetSystemTime API – current system date and time expressed in Coordinated Universal Time (UTC)

3. timeGetTime API – number of milliseconds that have elapsed since the system was started (up to 49.7 days)

4. GetTickCount API – same calculation as timeGetTime but using a different API

5. Stopwatch class API - a set of methods and properties to accurately measure elapsed time.

6. High Resolution Timer API – able to measure to less than one microsecond resolution

Some of these methods were mentioned in earlier responses

The article includes an example app you can use to test the different approaches. The main criteria in making a decision should be the precision required and the consistency of values in repeated tests.

Hope that helps