INF: Function to Show Date/Time as Hours, Minutes, and Seconds
PSS ID Number: Q115918
Article last modified on 07-31-1994

1.00 1.10 2.00

WINDOWS


---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Access versions 1.0, 1.1, and 2.0
---------------------------------------------------------------------

SUMMARY
=======

This article describes a sample user-defined Access Basic function called
GetTimeStr() that you can use to display a cumulative time amount as total
hours, minutes, and seconds.

The GetTimeStr() function may be useful when you need to display the sum of
the difference between the start time and the end time for a number of
records.

This article assumes that you are familiar with Access Basic and with
creating Microsoft Access applications using the programming tools provided
with Microsoft Access. For more information on Access Basic, please refer
to the "Introduction to Programming" manual in Microsoft Access version
1.x, or the "Building Applications" manual in version 2.0.

MORE INFORMATION
================

Given a table with each record having a start time and an end time, you can
find the difference between the two times with the an expression similar
to:

   =[end time] - [start time]

This expression will result in a decimal value because Microsoft Access
stores time as a number representing a fraction of 1 day. You can multiply
the resulting value by 24 to see the value in hours. For example, 12:00
P.M. (noon) is stored as 0.5 (half the day has expired). To see the value
in hours, multiply 0.5 by 24, to get 12 hours. 9:00 A.M. is stored as
0.375. Multiplying 0.375 by 24 results in 9 hours.

You can use the CSng() function to see the numeric equivalent of a time.
For example, the function

   ? CSng(#12:00pm#)

results in 0.5.

The following function demonstrates how to see the difference in two times
as a value in hours:

   ? CSng(#10:00am#-#9:00am#)*24

This function returns a value of 1.

Given a table with 100 records, all of which have a start time of 9:00 A.M.
and an end time of 10:00 A.M., you could sum the difference in times for
the records to get a numeric value of 4.1666666666667. Multiplying the
numeric value of 4.1666666666667 by 24 would return a value of 100 hours.

This works well for whole-hour values. However, numeric values that do not
represent whole hours, when multiplied by 24, will return decimal hour
values, which may not be what you want. For example, the function

   ? CSng(#10:23am#)*24

will return an hour value of 10.38333.

The GetTimeStr() function returns values broken out by hours, minutes, and
seconds.

The GetTimeStr() Function
-------------------------

Syntax: GetTimeStr(DateTime, Format)

Argument           Description
----------------------------------------------------------------------
DateTime           Date/Time data-type value that contains the data
                   you want to use.

Format             String of display format characters.
                   For consistency with Microsoft Access the following
                   symbols are allowed:

                   Symbol    Meaning
                   -------------------------
                   h         Display hours
                   n         Display minutes
                   s         Display seconds

Remarks: To display a unit with a fixed number of leading zeros,
         repeat the symbol as many times as needed. For example,
         to display hours to the hundreds unit, use the format
         "hhh."

         The first symbol will determine the units to be displayed.
         Each additional symbol must be delimited with a colon (:).
         Additional symbols will show additional units as remainders
         of the first unit. The following table demonstrates what will
         be displayed for various formats given a DateTime of 1.11
         (1.11 days):

         Format         Result
         -------------------------------------
         "h"            26 Hrs
         "h:n"          26 Hrs 38 Mins
         "h:n:s"        26 Hrs 38 Mins 24 Secs
         "n"            3996 Mins
         "n:s"          3996 Mins 24 Secs
         "s"            95904 Secs

         If the format does not begin with a valid symbol the result
         is "#Error?"

NOTE: In the following sample code, an underscore (_) at the end of a line
is used as a line-continuation character. Remove the underscore from the
end of the line when re-creating this code in Access Basic.

   Option Compare Database  'Use database order for string comparisons.
   Option Explicit

   Function GetTimeStr (dt As Variant, frmt As String) As String

     Dim totalsec As Double
     Dim hrs As Variant
     Dim mins As Variant
     Dim secs As Variant
     Dim timestr As String

     Dim p As Integer
     Dim timefrmt As String

     If IsNull(dt) Then
       GetTimeStr = ""
       Exit Function
     Else
       totalsec = ((dt) * 86400)
     End If

     Select Case Left$(frmt, 1)
       Case "H"
         hrs = (totalsec \ 3600)
         mins = ((totalsec Mod 3600)) \ 60
         secs = (totalsec Mod 60)
       Case "N"
         hrs = 0
         mins = ((totalsec \ 24))
         secs = (totalsec Mod 60)
       Case "S"
         hrs = 0
         mins = 0
         secs = (totalsec)
       Case Else
         GetTimeStr = "#Error?"
         Exit Function
     End Select

     timestr = ""

     Do Until Len(frmt) = 0

       p = InStr(frmt, ":")

       If p = 0 Then
         timefrmt = frmt
         frmt = ""
       Else
         timefrmt = Left$(frmt, (p - 1))
         frmt = Mid$(frmt, (p + 1))
       End If

       Select Case Left$(timefrmt, 1)
         Case "H"
           timestr = timestr & (Format$(hrs, String$(Len(timefrmt),_
           "0"))  & " Hrs ")
         Case "N"
           timestr = timestr & (Format$(mins, String$(Len(timefrmt),_
           "0")) & " Mins ")
         Case "S"
           timestr = timestr & (Format$(secs, String$(Len(timefrmt),_
           "0")) & " Secs")
       End Select

     Loop

     GetTimeStr = timestr

   End Function

Additional reference words: 1.00 1.10 2.00
KBCategory:
KBSubcategory: PgmOthr

=============================================================================

Copyright Microsoft Corporation 1994.
