Time Value Formatting Beyond Twenty Four Hours

Introduction.

The standard time format hh:nn:ss displays values up to 23 hours, 59 minutes, and 59 seconds (23:59:59). After that, it restarts at 00:00:01 one second past midnight (00:00:00) on the next day. Using the built-in Format() function, it is not possible to display time values beyond 24 hours, such as 45:30:15 (45 hours, 30 minutes, 15 seconds).

So, how do we format time values that exceed twenty-four hours? This requirement often arises in practical applications, such as calculating an employee’s total hours worked in a week (40:30:15), or an IT firm employee’s total internet browsing time for a month (115:47:12) extracted from server logs.

To achieve this, we need a custom function. The following VBA function accepts time values in a suitable format and produces the desired output. You can copy and paste the code into a Standard Module in your project and use it wherever extended time formatting is required. The FormatTime() Function Code.

Public Function FormatTime(ByVal StartDateTime As Variant, Optional ByVal EndDateTime As Variant = CDate(0)) As String
Dim v1 As Double, hh As Integer, nn As Integer, ss As Integer
'-------------------------------------------------------------------------
'Remarks: Time-Format function for time value beyond 23:59:59
'Author : a.p.r.pillai
'Date   : August-2016
'Rights : All Rights Reserved by www.msaccesstips.com
'
'Input options
'1. StartDatetime & EndDateTime (both date/time values or both Time Values)
'2. StartDateTime = Date/Time Value and EndDateTime value=0
'3. StartDateTime = Time Value in decimal format – e.g. 0.999988425925926) for TimeValue("23:59:59") or less
'4. StartDateTime = Time Value in Seconds – e.g. 86399 for Timevalue("23:59:59")*86400
'-------------------------------------------------------------------------
'VALIDATION SECTION

'If Input Value is in whole seconds (TimeValue*86400)
'for applications like internet Server log time etc.
'Option4

On Error GoTo FormatTime_Err

If TypeName(StartDateTime) = "Long" Then
   GoTo Method2
End If

If EndDateTime > 0 And EndDateTime < StartDateTime Then
'End-Date is less than Start-Date
   MsgBox "Error: End-Date < Start Date - Invalid", vbCritical, "Format_Time()"
ElseIf (EndDateTime > StartDateTime) And (StartDateTime > 0) Then
'option 1
   If TypeName(StartDateTime) = "Date" And TypeName(EndDateTime) = "Date" Then
     v1 = EndDateTime - StartDateTime
     GoTo Method1
   End If
ElseIf StartDateTime > 0 And EndDateTime = 0 Then
'option 2
'Is it Today's date & Time
   If Int(StartDateTime) = Int(Now()) Then
      'Remove Date number and take only time value
      v1 = StartDateTime - Int(StartDateTime)
   Else
   'Option 3
      'Assumes Value is Time-Value in decimal format
      v1 = StartDateTime
   End If
End If

'Option 1 to 3
Method1:
hh = Int(v1 * 24)
nn = Int((v1 - hh / 24) * 1440)
ss = Round((v1 - (hh / 24 + nn / 1440)) * 86400, 0)
FormatTime = Format(hh, "00:") & Format(nn, "00:") & Format(ss, "00")
Exit Function

'Time Input in Seconds
'Option 4
Method2:
v1 = StartDateTime
hh = Int(v1 / 3600)
nn = Int((v1 - (hh * 3600#)) / 60)
ss = v1 - (hh * 3600# + nn * 60)
FormatTime = Format(hh, "00:") & Format(nn, "00:") & Format(ss, "00")

FormatTime_Exit:
Exit Function

FormatTime_Err:
MsgBox "Error: " & Err & " - " & Err.Description, , "FormatTime()"
Resume FormatTime_Exit

End Function

Trial Run of Code.

Let’s go through a few examples to understand how to correctly pass parameter values to the FormatTime() function. You can try the following sample runs by typing them directly into the VBA Immediate (Debug) Window.

The FormatTime() function accepts either a Date & Time value or a Time value as its primary parameter. The second parameter is optional. The following rules apply when parameter values are passed to the function:

1. When both parameters are entered both should be either Date/Time Values or both Time Values only.  The difference in time will be calculated by subtracting the first parameter value from the second parameter.

2. The second parameter is optional. If omitted, then the Date value will be ignored when the Date/Time value is passed in the first parameter. The time value will be formatted and displayed.

3. The first parameter can be a time value in decimal format (e.g., 0.999988425925926 ). Omit the second parameter.

4. The first parameter is acceptable as a time value in Seconds (e.g. 86399). Omit the second parameter.

Example-1:

Both Parameter values are Date/Time Values. The second Parameter value should be greater than the first parameter value.

StartDT = DateAdd("d",-2,Now()) = 8/27/2016 7:38:22 PM 

EndDT = Now() = 8/29/2016 7:41:13 PM

? FormatTime(StartDT,EndDT)

Result: 48:02:51

Example-2:

First parameter Date/Time Value, second parameter is optional and omitted.

SDateTime=Now() or DateVallue("08/29/2016")+TimeValue("18:30:15")

? FormatTime(SDateTime)

Result: 18:30:15

Example-3:

When the first parameter is passed as a numeric time value (for example, 2.00197916667094), the FormatTime() function interprets it correctly. In this case, the result will be 48:02:51.

This decimal value represents the difference between two Date/Time values (End Date/Time minus Start Date/Time). The result is expressed as a decimal number, where:

• The whole number represents the number of days (e.g., 2 days × 24 hours = 48 hours).

• The fractional part represents the time of day, converted into Hours:Minutes:Seconds.

Such time values often appear as the result of summarizing multiple records from a Table or Query, where the difference between start and end times is accumulated.

StartDT = 2.00197916667094 

? FormatTime(StartDT)

Result: 48:02:51

The whole number 2 on the left side of the decimal point is the number of days and it will be ignored, when the second parameter is omitted.

You can create a time number similar to the one above for testing this function. To do that we will look at a few basics on the time value to understand them better.

1 Day = 24 hours. 1 Hour = 60 Minutes. 1 Minute = 60 Seconds. So 1 Day = 24 * 60 * 60 = 86400 Seconds.

1 second before mid-night = 86400-1 = 86399 seconds = 23:59:59

To convert the time 23:59:59 (86399) into the internal representation of time value, divide 86400 by 86399 (86399/86400). Result: 0.999988425925926. The time 23:59:59 is stored in computer memory in this form. When combined with the current Date-number, it will be like 42612.999988425925926 for 08/30/2016 23:59:59

Let us input this time number alone into our function and try out the result.

? FormatTime(0.999988425925926)

Result: 23:59:59

Example-4:

So, if you want to convert a Time number into Seconds, then multiply it by 86400. 0.999988425925926 * 86400 = 86399

You can input seconds as a time number as the first parameter to get it formatted in Hours:Minutes:Seconds.

? FormatTime(86399)

Result: 23:59:59

If you want larger values for testing this Function, try the following examples.

SD = 86399+86400+86000
   = 258799 seconds

SD = SD/86400
   = 2.9953587962963 time number

? FormatTime(SD)

Result: 71:53:19

Now, let us try passing the time value directly in seconds.
(For example, assume this value represents the total internet browsing time summarized in seconds.)

When a time value is provided in seconds, the FormatTime() function converts it into the proper format of Hours:Minutes:Seconds. This is particularly useful when server logs or other systems record usage duration in raw seconds, and we want to present it in a more readable form, such as 115:47:12.

SD= 258799

? FormatTime(SD)

Result: 71:53:19

If you maintain a library database, you can move this function there so that you don’t need to copy the code into every other database.

A library database is a common repository containing your custom functions, modules, or wizards that can be referenced by other projects. Using this approach allows you to reuse functions across multiple databases without duplicating code. For a detailed discussion on this topic, see the page MS-Access and Reference Library.

We welcome your comments or suggestions for improving the FormatTime() function. Please leave them in the Comments Section at the end of this post.

• Sum, Min, Max, Avg, ParamArray
• Useful Report Functions
• Cardinal Text Format in Access
• Auto-Numbering in Query Column

Opening Multiple Instances of Form in Memory

Introduction.

1. Over the past few weeks, we have been learning about the dot (.) separator and the exclamation (!) symbol in VBA object references. Now, we will explore some interesting techniques with Forms in VBA, specifically:

   • How to call a function procedure embedded in a form’s module (class module) from the code outside that form?

   • How to open multiple instances of a single Microsoft Access Form in memory, each displaying different information?

   For example, the Screenshot below shows two instances of the Employees Form. The first instance is behind the second, displaying employee details for IDs 4 and 5. Click the image to enlarge for a clearer view.

   

   Right-click to open Large Image in New Tab/Window.

   
   

   Calling the Form's Class Module Public Function.

2. How to call a Function Procedure on the Form's Class Module, from outside the Form?

   Call the Function from a Standard Module,  from the Module of another form, or from the VBA Debug Window (Immediate Window).  The target form must be opened in memory to call the function procedure of the form from outside.

Function procedures in a form module are useful for avoiding code duplication. They can be called from subroutines in different parts of the same form, from a command button click, or from other event procedures within the form. A function procedure in a form module can perform calculations, validation checks, update information, or act as a standalone operation—such as the one we will use in our sample Employees Form.

All event procedures in a form module are automatically declared as Private Subroutines, and they begin and end with standard statements, as shown in the sample below. Any custom VBA code that performs specific tasks should be placed within this block:

Private Sub Command8_Click()
.
.
End Sub

A Private subroutine or function is limited in scope to the module in which it is declared and cannot be called from outside that module. Declaring procedures as private is essential to avoid name conflicts with procedures of the same name in other Class Modules or Standard Modules.

To call a function procedure from outside a Form, the Function must be declared as Public in the Form’s module.

To perform a trial run of the above trick, you need the Employees Table and a Form.

1. Import the Employees Table from the Northwind sample database.

2. Click on the Employees Table to select it.

3. Click on Create Ribbon.

4. Select the Form option and create a Form for Employees Table, in the format shown above.

5. Save the Form with the name frmEmployees.

6. Open the frmEmployees Form in Design View.  Set the Form Property 'Has Module' Value to Yes.

7. Select the Design Menu and select VBA Code from the Tools button group to open the Form Module.

8. Copy the following VBA code and paste it into the VBA Module of the Form.

   Public Function in Form Class Module.

   Public Function GetFirstName(ByVal EmpID As Integer) As String
   Dim rst As Recordset, crit As String
   Dim empCount As Integer
   
   'get total count of employees in the Table
   empCount = DCount("*", "Employees")
   
   'validate employee code
   If EmpID > 0 And EmpID <= empCount Then
   
       crit = "ID = " & EmpID
       Set rst = Me.RecordsetClone
       rst.FindFirst crit
       
       If Not rst.NoMatch Then
             
        	Me.Bookmark = rst.Bookmark
           GetFirstName = rst![First Name]
       End If
       
          rst.close
          Set rst = Nothing
       
     Else
       MsgBox "Valid Employee IDs: 1 to " & empCount
   End If
   
   End Function
   

9. Save and Close the Form.

Notice that the starting line of the above function is declared as Public.

The function GetFirstName() accepts EmployeeID as a parameter, locates the corresponding record on the form, and makes that record the current record. If the search is successful, the function returns the employee’s first name to the calling procedure. If the search fails, it displays a warning message indicating that the EmployeeID provided is not within the range of IDs in the Employees table.

Next, we need a program in a Standard Module that calls the GetFirstName() function from the frmEmployees form module. This program will also demonstrate how to create multiple instances of a Microsoft Access form, allowing you to open them in memory and access their properties, methods, or control contents independently.

1. Open VBA Editing Window (Alt+F11).

2. Select the Module option from the Insert Menu and add a new Standard Module.

3. Copy and paste the following VBA Function code into the new Module.

   Call GetFirstName() from the Standard Module.

   Public Function frmInstanceTest()
   
         Dim frm As New Form_frmEmployees '1st Form instance
   
         Dim frm2 As New Form_frmEmployees '2nd instance declaration
   
         Dim Name1 As String, Name2 As String
   
     frm.Visible = True 'make the instance visible in Application Window
     frm2.Visible = True '2nd instance visible
   
     Name1 = frm.GetFirstName(4) 'Call the GetFirstName of Employee ID 4
     
     Name2 = frm2.GetFirstName(5) ''Call the GetFirstName of Employee ID 5
   
   'pause execution of this code to view
   'the Employees Form instances in Application Window.
   
   Stop
   
     MsgBox "Employees " & Name1 & ", " & Name2
     
   End Function

Trial Run of Function frmInstanceTest()

Let us run the code and view the result in the Application Window.

1. Click somewhere within the body of the frmInstanceTest() function and press the F5 key to run the code.

   The program will pause at the Stop statement, allowing you to view the Access application window, where multiple instances of the frmEmployees form are open in Normal View, with one instance overlapping the other.

2. Press Alt+F11 to display the Application Window, where both instances of the Form are visible, the second form overlapping the first one.

3. Click and hold the Title Bar area at the top and drag it to the right to make part of the form behind visible.

   Observe the employee records on both forms—they are different: one displays Employee ID 4, and the other displays Employee ID 5. Notice the title bar of both forms; they both show the same form name, frmEmployees. Now, return to the program and continue running the code to complete the task.

4. Press Alt+F11 again to switch back to the VBA Window and press the F5 key one more time to continue executing the remaining lines of code.

   The Message Box appears in the Application Window, displaying the Employee names Mariya and Steven together.  When you click the OK MsgBox Button, the frmEmployee form instances disappear from the Application Window.

5. Click the OK button on the MsgBox.

Note: Pay special attention to the Stop statement placed above the MsgBox() function at the end of the code. The Stop statement halts VBA execution at that point. While it is typically used during debugging to trace logical errors or make corrections, here it serves a different purpose: it pauses the program so that we can switch to the Access application window and view both open instances of the frmEmployees form.

If we relied on the MsgBox() function alone, the code would still pause, but the message box would remain on top of the forms. This would prevent us from dragging the front form aside to view the one behind it. By using the Stop statement, we gain the flexibility to examine both instances directly in the application window.

If we don't create a pause in the code execution, both instances of the form are closed immediately when the program ends.  In that case, we will not be able to view the forms.  Since it is a trial run, we would like to know what is happening in the program. It is not necessary to make the Form instances visible before calling the Function GetFirstName ().

The VBA Code Line by Line.

Let’s take a closer look at each line of code in the frmInstanceTest() function. While brief hints are already included within the code, explaining a few points in detail will make them clearer. We’ll begin with the first two Dim statements.

Dim frm As New Form_frmEmployees

Dim frm2 As New Form_frmEmployees

In the above Dim statement, you can see that the New keyword is followed by the object reference. The object name is our frmEmployees prefixed by the direct Object Class name FORM, followed by an underscore character separation (Form_) to the frmEmployees Form name (Form_frmEmployees).  These Dim statements themselves open two instances of the frmEmployees in memory.   Form instances opened in this way are not immediately visible in the Application Window.  If we need them to be visible, then make them visible with another statement.

Next, we declared two String Variables: Name1 & Name2 to hold the names returned by the GetFirstName() method.

Next two statements: frm.Visible=True and frm2.Visible=True, makes both instances of the frmEmployees Form visible in the Application Window, for information purposes only.

In the next two lines of code, we are calling the GetFirstName() method of the first and second instances of the frmEmployees to search, find, and return the First Names of employee codes 4 and 5.

Default Instance and Other Instances.

The default instance of a Form is opened in the following manner in programs for accessing their Properties, Methods, and Controls.  These styles of statements are always used to open a form in programs. The default instance of the Form will be automatically visible in the Application Window.

Dim frm as Form 'define a Form class object
DoCmd.OpenForm "frmEmployees", vbNormal 'open frmEmployees in Memory
Set frm3 = Forms!frmEmployees ' attach it to the frm3 object

Assume that we have opened frm & frm2 instances first in memory before the default instance through the above code.  How do we address those three instances in a program to do something?  Let us forget about the frm, frm2, and frm3 object references, for now, we will go with the straight method, like the one given below:

name3 = Forms![frmEmployees].GetFirstName(5) 'target form in memory is the default instance
'OR
name3 = Forms("frmEmployees").GetFirstName(5) 
'OR
name3 = Forms(2).GetFirstName(5) ' this is the third and default instance

The other two instances in memory cannot be referenced like the first two default methods, using the name of the form. You have to use only the index number of the Forms collection to address the other two instances.

name1 = Forms(0).GetFirstName(3)
name2 = Forms(1).GetFirstName(6)

A Shortcut Method.

There is a shortcut method you can use to run the GetFirstName() Method of the frmEmployees Form from the debug window (Ctrl+G).  Type the following command in the Debug Window and press Enter Key:

? form_frmEmployees.GetFirstName(5)
'Result: Steven
'OR
X = form_frmEmployees.GetFirstName(5) 

When we execute the above command, it opens an instance of the frmEmployees form in memory and calls the GetFirstName() function with Employee Code 5 as the parameter. The GetFirstName() function searches for the record, finds it, returns the employee’s first name to the calling program, and then closes the form.

Tip: Even after the form is closed, the current record—Employee ID 5 in this case—remains the active record of the closed form’s last session.

You can verify this behavior by typing the following shortcut command in the Debug Window and pressing Enter:

? Forms!frmEmployees!EmployeeID

? form_frmEmployees![First Name]

'Result: Steven

A Fancy Approach.

In the above command, we didn't run the GetFirstName() method, but the current record's First Name field value is printed. If you want to get a little fancy with the command, then try this by typing it in the debug window and pressing the Enter Key:

MsgBox "First Name: " & form_frmEmployees.GetFirstName(8)
'OR
MsgBox "First Name: " & form_frmEmployees![First Name]

Or try the above command from a Command Button Click Event Procedure from another Form's Module, as given below.

Private Sub Command8_Click()
  MsgBox "First Name: " & Form_frmEmployees.GetFirstName(8)

End Sub

• Create 3D Headings on Form
• Reminder Ticker on Form
• Open Forms with Hyperlink in Listbox
• Overlaying Sub-Forms in Real-Time