Microsoft KB Archive/115918

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

= 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 -    &quot;h&quot;            26 Hrs &quot;h:n&quot;         26 Hrs 38 Mins &quot;h:n:s&quot;       26 Hrs 38 Mins 24 Secs &quot;n&quot;           3996 Mins &quot;n:s&quot;         3996 Mins 24 Secs &quot;s&quot;           95904 Secs

If the format does not begin with a valid symbol the result is &quot;#Error?&quot; 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 = &quot;&quot; Exit Function Else totalsec = ((dt) * 86400) End If

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

timestr = &quot;&quot;

Do Until Len(frmt) = 0

p = InStr(frmt, &quot;:&quot;)

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

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

Loop

GetTimeStr = timestr End Function

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

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

Copyright Microsoft Corporation 1994.