Because a Date/Time value is stored as a double-precision number, you may
receive incorrect formatting results when you try to manipulate Date/Time
values in an expression. This article shows you how to create expressions
and custom functions for displaying specific dates and calculating time
intervals.
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access versions 1.x and 2.0. For more information about Access Basic,
please refer to the "Introduction to Programming" manual in Microsoft
Access version 1.x or the "Building Applications" manual in Microsoft
Access version 2.0
To display specific dates, you can use the DateSerial() function to
manipulate the day, month, and year portions of a date. For example, you
can use the following expressions in the ControlSource property of a text
box or in the Debug window (or the Immediate window in versions 1.x and
2.0) to return specific dates:
Because a time value is stored as a fraction of a 24-hour day, you may
receive incorrect formatting results when you try to add, subtract,
multiply or divide time data greater than 24 hours.
For example, if you try to find the number of hours elapsed between two
dates by subtracting the values in Visual Basic, you may receive an
incorrect number. To demonstrate this, type the following code in the
Debug window (or Immediate window in version 2.0) and note that it
returns a value of 05:00 hours rather than the correct value of 53:00
hours:
To resolve formatting problems caused by time values greater than 24
hours, you can use the Int() and CSng() functions in Visual Basic to
separate a calculated time value into different variables for days,
hours, minutes, and seconds. For example, you can include the following
code fragment in a custom function to create separate time variables:
'-------------------------------------------------------------------
' This sample code separates a time interval into seven variables for
' the following values: days, hours, minutes, seconds, total time in
' hours, total time in minutes, and total time in seconds.
'
' The interval argument is flexible; it can be a single value, an
' expression, or a field reference.
'-------------------------------------------------------------------
Dim totalhours As Long, totalminutes As Long, totalseconds As Long
Dim days As Long, hours As Long, minutes As Long, seconds As Long
Dim interval As Variant
days = Int(CSng(interval))
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
minutes = totalminutes Mod 60
seconds = totalseconds Mod 60
You can use the totalhours, totalminutes, and totalseconds variables to
display a time value as a single unit of time. The days, hours, minutes,
and seconds variables enable you to break down a time value into portions
of time. To display time values in different formats, you can concatenate
these variables as demonstrated in the following sample functions:
The GetElapsedDays() function calculates the elapsed time between two
date/time values and displays the result in days.
The GetElapsedTime() function calculates the elapsed time between time
values and displays the result in days, hours, minutes, and seconds.
The GetTimeCardTotal() function sums a field of time values in a table
and displays the total in hours and minutes.
Field: Shipped Date
Show: True
Field: Order Date
Show: True
Field: ElapsedTime: GetElapsedDays([Shipped Date]-[Order Date])
Show: True
Run the query. Note that the ElapsedTime column displays the number of
days between the ShippedDate field (or Shipped Date in versions 1.x and
2.0) and OrderDate field (or Order Date in versions 1.x and 2.0) for
each record in the Orders table.
To create the GetElapsedTime() function, follow these steps:
Create a new table with the following structure and save it as TimeLog.
Table: TimeLog
-----------------------
Field Name: StartTime
Data Type: Date/Time
Format: General Date
Field Name: EndTime
Data Type: Date/Time
Format: General Date
View the TimeLog table in Datasheet view, enter the following three
records, and then close the table:
StartTime EndTime
--------------------------------------------
5/10/95 4:57:00 PM 5/15/95 2:38:00 AM
5/11/95 10:17:31 AM 5/24/95 6:05:00 PM
5/18/95 9:16:43 AM 5/19/95 5:03:00 PM
Create a module and type the following line in the Declarations
section:
Option Explicit
Enter the following function:
NOTE: In the following sample code, an underscore (_) is used as a
line-continuation character. Remove the underscore from the end of the
line when re-creating this code in Access Basic.
Function GetElapsedTime (interval)
Dim totalhours As Long, totalminutes As Long, totalseconds As _
Long
Dim days As Long, hours As Long, Minutes As Long, Seconds As Long
days = Int(CSng(interval))
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
Minutes = totalminutes Mod 60
Seconds = totalseconds Mod 60
GetElapsedTime = days & " Days " & hours & " Hours " & Minutes & _
" Minutes " & Seconds & " Seconds "
End Function
NOTE: The GetElapsedTime function requires that you pass it a date and
a time.
To test this function, create a new report based on the TimeLog table
using the AutoReport Wizard.
View the report in Design view.
Add an unbound text box to the TimeLog table's detail section and set
its properties as follows:
To create the GetTimeCardTotal() function, follow these steps:
Create a new table with the following structure and save it as
TimeCard.
Table: TimeCard
-----------------------
Field Name: Daily Hours
Data Type: Date/Time
Format: Short Time
View the TimeCard table in Datasheet view, enter the following four
records, and then close the table:
8:15
7:37
8:12
8:03
Create a module and type the following line in the Declarations
section if it's not already there:
Option Explicit
Type the following function:
Function GetTimeCardTotal ()
Dim db As Database, rs As Recordset
Dim totalhours As Long, totalminutes As Long
Dim days As Long, hours As Long, minutes As Long
Dim interval As Variant, j As Integer
Set db = dbengine.workspaces(0).databases(0)
Set rs = db.OpenRecordset("timecard")
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![Daily hours]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60
GetTimeCardTotal = totalhours &" hours and " &minutes &" minutes"
End Function
To test this function, type the following line in the Debug window (or
Immediate window in version 1.x and 2.0), and then press ENTER:
? GetTimeCardTotal ()
Note that the Debug window displays 32 hours and 7 minutes.
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top
