INF: How to Display Intervals Greater Than 24 Hours PSS ID Number: Q109320 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
When you are displaying an interval of greater than 24 hours in a calculated control, the interval will not be displayed correctly. Depending on the format of the control, you will see just the hours and minutes, or you will see an incorrect date, such as 12/31/1889.
This article demonstrates three sample functions you can add to a calculated control to correctly display intervals of greater than 24 hours.
MORE INFORMATION
NOTE: This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access.
To find the number of hours elapsed between two dates, you can subtract the values in Access Basic. For example, the sample code
StartDate=#6/1/93 8:00AM# EndDate=#6/1/93 1:00PM# ?Format(EndDate-StartDate,“hh:mm”)
will return a value of 05:00, which is the correct result.
However, the sample code
StartDate=#6/1/93 8:00AM# EndDate=#6/3/93 3:00PM# ? Format(EndDate-StartDate,“hh:mm”)
will return a value of 07:00, which is incorrect. The correct result is 55:00.
The following three sample functions will display the elapsed time correctly:
NOTE: In the following sample code, an underscore (_) is used as a line- continuation character. Remove the underscore when re-creating this code in Access Basic.
Open a module and enter the following in the Declarations section:
Option Explicit Global StartDate Global EndDate
Enter the following functions:
‘—————————————————’ This function accepts a time interval and returns ’ a value, such as “55:23” for 55 hours and 23 minutes. ’————————————————— Function HoursMinutes (Interval) Dim Hours As Long, Minutes As Integer If IsNull(Interval) Then HoursMinutes = Null Else Hours = Int(Interval * 24) Minutes = CInt((Interval * 24 - Hours) * 60) If Minutes = 60 Then Minutes = 0 Hours = hours + 1 End If HoursMinutes = Hours & “:” & Format(Minutes, “00”) End If End Function
‘—————————————————’ This function accepts a time interval and returns ’ a value, such as “1 Day 23 Hours.” ’————————————————— Function DaysHours (Interval) Dim Days As Long, Hours As Long If IsNull(Interval) Then DaysHours = Null Else Days = Int(Interval) Hours = CInt((Interval - Days) * 24) If Hours = 24 Then Hours = 0 Days = Days + 1 End If DaysHours = Days & IIf(Days = 1, " Day “,” Days ") & _ Hours & IIf(Hours = 1, " Hour“,” Hours") End If End Function
‘—————————————————’ This function accepts a time interval and returns ’ a value, such as “3 Days 17:43”. ’————————————————— Function DaysHoursMinutes (Interval) Dim Days As Long, Remainder As Double If IsNull(Interval) Then DaysHoursMinutes = Null Else Days = Int(Interval) Remainder = Interval - Days If Remainder > #23:59:59# Then Remainder = 0 Days = Days + 1 End If DaysHoursMinutes = Days & IIf(Days = 1, " Day “,” Days ") & _ Format(Remainder, “hh:mm”) & " Hours" End If End Function
Open a new module, and type the following in the module’s Immediate window:
StartDate=#6/1/93 8:00AM# EndDate=#6/3/93 3:05PM# ?HoursMinutes(EndDate-StartDate)
The HoursMinutes() function will return a value of 55:05.
Type:
?DaysHours(EndDate-StartDate)
The DaysHours() function will return the value “2 Days 7 Hours.”
Type:
?DaysHoursMinutes(EndDate-StartDate)
The DaysHoursMinutes() function will return the value “2 Days 07:05.”
REFERENCES
For more information on calculating time differences, query on the following words here in the Microsoft Knowledge Base:
calculate and timevalue
Additional reference words: 1.00 1.10 2.00 KBCategory: KBSubcategory: PgmOthr
=================================================================
Copyright Microsoft Corporation 1994.
