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.
