You are currently offline, waiting for your internet to reconnect

Functions for calculating and for displaying Date/Time values in Access

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q210604
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY
Because a Date/Time value is stored as a double-precision number, you mayreceive incorrect formatting results when you try to manipulate Date/Timevalues in an expression. This article demonstrates how to create expressions and custom functions for displaying specific dates and for calculating time intervals.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
MORE INFORMATION

Displaying specific dates

To display specific dates, you can use the DateSerial() function tomanipulate the day, month, and year portions of a date. For example, youcan use the following expressions in the ControlSource property of a text box or in the Immediate window to return specific dates:
  • The current month:
    DateSerial(Year(Date()), Month(Date()), 1)
  • The next month:
    DateSerial(Year(Date()), Month(Date()) + 1, 1)
  • The last day of the current month:
    DateSerial(Year(Date()), Month(Date()) + 1, 0)
  • The last day of the next month:
    DateSerial(Year(Date()), Month(Date()) + 2, 0)
  • The first day of the previous month:
    DateSerial(Year(Date()), Month(Date())-1,1)
  • The last day of the previous month:
    DateSerial(Year(Date()), Month(Date()),0)
  • The first day of the current quarter:
    DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 1)
  • The last day of the current quarter:
    DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 4, 0)
  • The first day of the current week (assuming Sunday = day 1):
    Date() - WeekDay(Date()) + 1
  • The last day of the current week:
    Date() - WeekDay(Date()) + 7
  • The first day of the current week (using settings in Options dialogbox):
    Date() - WeekDay(Date(), 0) + 1
  • The last day of the current week:
    Date() - WeekDay(Date(), 0) + 7
For more information about calculating the fiscal year or the fiscal month, click the following article number to view the article in the Microsoft Knowledge Base:
210249 How to get the fiscal year or month of a particular date in Access 2000
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Calculating time intervals

Because a time value is stored as a fraction of a 24-hour day, you mayreceive 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 twodates by subtracting the values in Visual Basic, you may receive anincorrect number. To demonstrate this, type the following code in theImmediate window and note that it returns a value of 0:00 hours rather than the correct value of 53:00 hours:
   StartDate=#6/1/93 8:00AM#   EndDate=#6/3/93 1:00PM#   ?Format(EndDate-StartDate,"hh:mm")

To resolve formatting problems caused by time values greater than 24hours, 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 todisplay 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 portionsof time. To display time values in different formats, you can concatenatethese 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.

GetElapsedDays() sample function

To create the GetElapsedDays() function, follow these steps:
  1. Open the sample database Northwind.mdb.
  2. Create a module and type the following line in the Declarationssection if it is not already there:
     Option Explicit
  3. Type the following function:
     Function GetElapsedDays (interval)    Dim days As Long    days = Int(CSng(interval))    GetElapsedDays = days & " Days " End Function
  4. To test this function, create a new query based on the Orders table.
  5. In the QBE grid, add the following fields.
        Field: ShippedDate    Show: True    Field: OrderDate    Show: True    Field: ElapsedTime: GetElapsedDays([ShippedDate]-[OrderDate])    Show: True
  6. Run the query. Note that the ElapsedTime column displays the number of days between the ShippedDate field and OrderDate field for each record in the Orders table.

GetElapsedTime() sample function

To create the GetElapsedTime() function, follow these steps:
  1. 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
  2. View the TimeLog table in Datasheet view, enter the following threerecords, 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
  3. Create a module and type the following line in the Declarations section:
     Option Explicit
  4. Enter the following function:
     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.
  5. To test this function, create a new report based on the TimeLog table using the AutoReport Wizard.
  6. View the report in Design view.
  7. Add an unbound text box to the TimeLog table's detail section and setits properties as follows:
        Textbox    ---------------    Name: ElapsedTime    ControlSource: =GetElapsedTime([EndTime]-[StartTime])    Width: 3 inches
  8. Preview the report. Note that each record displays the total elapsedtime in days, hours, minutes, and seconds.

GetTimeCardTotal() sample function

To create the GetTimeCardTotal() function, follow these steps:

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

  1. 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
  2. View the TimeCard table in Datasheet view, enter the following fourrecords, and then close the table:
        8:15    7:37    8:12    8:03
  3. Create a module and type the following line in the Declarationssection if it's not already there:
     Option Explicit
  4. Type the following function:
     Function GetTimeCardTotal () Dim db As DAO.Database, rs As DAO.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
  5. To test this function, type the following line in the Immediate Window, and then press ENTER:
     ?GetTimeCardTotal()

    Note that the Immediate Window displays 32 hours and 7 minutes.
REFERENCES
For more information about calculating Date/Time values, click the following article number to view the article in the Microsoft Knowledge Base:
210276 Storing, calculating, and comparing Date/Time data
fractional ACC2007
Properties

Article ID: 210604 - Last Review: 01/06/2007 00:26:15 - Revision: 5.2

  • Microsoft Office Access 2007
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
  • Microsoft Access 2000 Standard Edition
  • kbhowto kbprogramming KB210604
Feedback
> '" + "'><\/script>");