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

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

meLady
P: 27
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????
Apr 14 '07 #1
Share this Question
Share on Google+
14 Replies

ADezii
Expert 5K+
P: 8,745
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:
Expand|Select|Wrap|Line Numbers
  1. 'Declare in a Standard Code Module
  2. Public Declare Function timeGetTime Lib "winmm.dll" () As Long
Basic Code Template:
Expand|Select|Wrap|Line Numbers
  1. Dim lngStartTime As Long, lngEndTime As Long, T As Long
  2.  
  3. lngStartTime = timeGetTime()
  4.  
  5. For T = 1 To 20000
  6.   Debug.Print T / 125
  7. Next
  8.  
  9. lngEndTime = timeGetTime()
  10.  
  11. Debug.Print "It took " & CStr(lngEndTime - lngStartTime) & " milliseconds to complete this process"
  12. Debug.Print "It took " & CStr((lngEndTime - lngStartTime) / 1000) & " seconds to complete this process"
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. It took 5125 milliseconds to complete this process
  2. It took 5.125 seconds to complete this process
Apr 15 '07 #2

meLady
P: 27
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.
Apr 15 '07 #3

meLady
P: 27
any reply to my question!
Apr 16 '07 #4

ADezii
Expert 5K+
P: 8,745
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.
Apr 16 '07 #5

meLady
P: 27
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.
Apr 17 '07 #6

meLady
P: 27
Hello ADezii
Apr 20 '07 #7

ADezii
Expert 5K+
P: 8,745
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.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim intHundrethsOfSeconds As Integer, intNoOfSeconds
  3. Dim intNoOfMinutes As Integer, intNoOfHours As Integer
  4.  
  5. Set MyDB = CurrentDb()
  6. Set MyRS = MyDB.OpenRecordset("Total Time", dbOpenDynaset)
  7.  
  8. MyRS.MoveFirst
  9.  
  10. Do While Not MyRS.EOF
  11.   intNoOfHours = intNoOfHours + Val(Left(MyRS![Timing], 2))
  12.   intNoOfMinutes = intNoOfMinutes + Val(Mid(MyRS![Timing], 4, 2))
  13.   intNoOfSeconds = intNoOfSeconds + Val(Mid(MyRS![Timing], 7, 2))
  14.   intHundrethsOfSeconds = intHundrethsOfSeconds + Val(Right(MyRS![Timing], 2))
  15.     MyRS.MoveNext
  16. Loop
  17.  
  18. MyRS.Close
  19.  
  20. Debug.Print Format(intNoOfHours, "00") & ":" & Format(intNoOfMinutes, "00") & ":" & _
  21.             Format(intNoOfSeconds, "00") & "," & Format(intHundrethsOfSeconds, "00")
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. 00:01:50,201 which is equivqlent to 00:01:52,01
NOTE: The code assumes a consistent Format of HH:MM:SS,hh
Apr 21 '07 #8

meLady
P: 27
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 (^_^)
Apr 21 '07 #9

ADezii
Expert 5K+
P: 8,745
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.
Apr 21 '07 #10

ADezii
Expert 5K+
P: 8,745
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim intHundrethsOfSeconds As Integer, intNoOfSeconds
  3. Dim intNoOfMinutes As Integer, intNoOfHours As Integer
  4. Dim MySQL As String
  5.  
  6. MySQL = "Select * From [Total Time] Where [Process Type]='Active'"
  7.  
  8. Set MyDB = CurrentDb()
  9. Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
  10.  
  11. MyRS.MoveFirst
  12.  
  13. Do While Not MyRS.EOF
  14.   intNoOfHours = intNoOfHours + Val(Left(MyRS![Timing], 2))
  15.   intNoOfMinutes = intNoOfMinutes + Val(Mid(MyRS![Timing], 4, 2))
  16.   intNoOfSeconds = intNoOfSeconds + Val(Mid(MyRS![Timing], 7, 2))
  17.   intHundrethsOfSeconds = intHundrethsOfSeconds + Val(Right(MyRS![Timing], 2))
  18.     MyRS.MoveNext
  19. Loop
  20.  
  21. MyRS.Close
  22.  
  23. Debug.Print Format(intNoOfHours, "00") & ":" & Format(intNoOfMinutes, "00") & ":" & _
  24.             Format(intNoOfSeconds, "00") & "," & Format(intHundrethsOfSeconds, "00")
Apr 21 '07 #11

ADezii
Expert 5K+
P: 8,745
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strSomeString As String
  2. strSomeString = fCalculateTimeTotals("<your process type>")
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalculateTimeTotals(strProcessType As String) As String
  2. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  3. Dim intHundrethsOfSeconds As Integer, intNoOfSeconds
  4. Dim intNoOfMinutes As Integer, intNoOfHours As Integer
  5. Dim MySQL As String
  6.  
  7. MySQL = "Select * From [Total Time] Where [Process Type]='" & strProcessType & "'"
  8.  
  9. Set MyDB = CurrentDb()
  10. Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
  11.  
  12. MyRS.MoveFirst
  13.  
  14. Do While Not MyRS.EOF
  15.   intHundrethsOfSeconds = intHundrethsOfSeconds + Val(Right(MyRS![Timing], 2))
  16.   intNoOfSeconds = intNoOfSeconds + Val(Mid(MyRS![Timing], 7, 2))
  17.   intNoOfMinutes = intNoOfMinutes + Val(Mid(MyRS![Timing], 4, 2))
  18.   intNoOfHours = intNoOfHours + Val(Left(MyRS![Timing], 2))
  19.     MyRS.MoveNext
  20. Loop
  21.  
  22. MyRS.Close
  23.  
  24. 'Debug.Print Format(intNoOfHours, "00") & ":" & Format(intNoOfMinutes, "00") & ":" & _
  25.             Format(intNoOfSeconds, "00") & "," & Format(intHundrethsOfSeconds, "00")
  26.  
  27. 'Now, check for overages in each set (1st Hundreths)
  28. If intHundrethsOfSeconds >= 100 Then
  29.   intNoOfSeconds = intNoOfSeconds + Int(intHundrethsOfSeconds / 100)
  30.   intHundrethsOfSeconds = (intHundrethsOfSeconds - Int(intHundrethsOfSeconds / 100) * 100)
  31. End If
  32.  
  33. 'Let's check Seconds now for overages
  34. If intNoOfSeconds >= 60 Then
  35.   intNoOfMinutes = intNoOfMinutes + Int(intNoOfSeconds / 60)
  36.   intNoOfSeconds = (intNoOfSeconds - Int(intNoOfSeconds / 60) * 60)
  37. End If
  38.  
  39. 'Let's check Minutes now for overages
  40. If intNoOfMinutes >= 60 Then
  41.   intNoOfHours = intNoOfHours + Int(intNoOfMinutes / 60)
  42.   intNoOfMinutes = (intNoOfMinutes - Int(intNoOfMinutes / 60) * 60)
  43. End If
  44.  
  45. 'Debug.Print Format(intNoOfHours, "00") & ":" & Format(intNoOfMinutes, "00") & ":" & _
  46.             'Format(intNoOfSeconds, "00") & "," & Format(intHundrethsOfSeconds, "00")
  47. 'Debug.Print "---------------------"
  48.  
  49. fCalculateTimeTotals = Format(intNoOfHours, "00") & ":" & Format(intNoOfMinutes, "00") & ":" & _
  50.                        Format(intNoOfSeconds, "00") & "," & Format(intHundrethsOfSeconds, "00")
  51. End Function
NOTE: Let me know how you make out.
Apr 22 '07 #12

P: 1
@ADezii
Can I get the file which this was created
3 Weeks Ago #13

100+
P: 189
https://docs.microsoft.com/en-us/win...ectedfrom=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.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit 
  2. Private Declare PtrSafe Function QueryPerformanceCounter Lib "Kernel32" (X As Currency) As Boolean
  3. Private Declare PtrSafe Function QueryPerformanceFrequency Lib "Kernel32" (X As Currency) As Boolean
  4. Dim Freq As Currency
  5. Dim Overhead As Currency
  6. Dim C1 As Currency, C2 As Currency, ms As Currency
  7. Dim StopWatch As Boolean
  8. Dim h As Currency, m As Currency, s As Currency
  9. Dim hms As String
  10.  
  11. 'the processing time with high accuracy of milliseconds
  12. Public Sub StopWatchStart()
  13.     If QueryPerformanceCounter(C1) Then
  14.         QueryPerformanceCounter C2
  15.         QueryPerformanceFrequency Freq
  16.         Overhead = C2 - C1
  17.     End If
  18.     QueryPerformanceCounter C1
  19. End Sub
  20.  
  21. Public Sub StopWatchStop()
  22.     QueryPerformanceCounter C2
  23.     ms = (C2 - C1 - Overhead) / Freq '[sec]
  24.     h = Int(ms / 60 / 60)
  25.     m = Int((ms - h * 60 * 60) / 60)
  26.     s = ms - h * 60 * 60 - m * 60
  27.     hms = Format(h, "00") & ":" & Format(m, "00") & ":" & Format(s, "00.00")
  28. End Sub
  29.  
  30. Public Sub StopWatchShow(Optional Caption As String)
  31.     MsgBox Caption & " " & hms
  32. End Sub
  33.  
  34. Private Sub UserForm_Initialize()
  35.     StopWatch = False
  36. End Sub
  37.  
  38. Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  39.     If StopWatch = False Then
  40.         StopWatch = True
  41.         StopWatchStart
  42.     End If
  43. End Sub
  44.  
  45. Private Sub CommandButton1_Click()
  46.     StopWatchStop
  47.     StopWatch = False
  48.     StopWatchShow "Time involved in input :"
  49. End Sub
  50.  
  51.  
3 Weeks Ago #14

isladogs
Expert
P: 40
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
2 Weeks Ago #15

Post your reply

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