ACC: Functions for Calculating and Displaying Date/Time Values

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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 demonstrates 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

MORE INFORMATION

Displaying Specific Dates

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:

• 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 dialog box):

        Date() - WeekDay(Date(), 0) + 1
  

• The last day of the current week:

        Date() - WeekDay(Date(), 0) + 7
  

   ARTICLE-ID: Q132101
   TITLE     : ACC: How to Get the Fiscal Year/Month of a Particular Date

Calculating Time Intervals

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:

   StartDate=#6/1/93 8:00AM#
   EndDate=#6/3/93 1:00PM#
   ?Format(EndDate-StartDate,"hh:mm")

   '-------------------------------------------------------------------
   ' 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

• 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.(or NWIND.MDB in versions 1.x and 2.0).

2. Create a module and type the following line in the Declarations section 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.

         In Microsoft Access version 7.0 and 97:
   

            Field: ShippedDate
               Show: True
            Field: OrderDate
               Show: True
            Field: ElapsedTime: GetElapsedDays([ShippedDate]-[OrderDate])
               Show: True
   
         In Microsoft Access versions 1.x and 2.0:
   
            Field: Shipped Date
               Show: True
            Field: Order Date
               Show: True
            Field: ElapsedTime: GetElapsedDays([Shipped Date]-[Order Date])
               Show: True
   
   

6. 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.

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 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
   
   

3. Create a module and type the following line in the Declarations section:

         Option Explicit
   

4. 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.
   
   

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 set its properties as follows:

         Textbox:
            Name: ElapsedTime
            ControlSource: =GetElapsedTime([EndTime]-[StartTime])
            Width: 3 inches
   
   

8. Preview the report. Note that each record displays the total elapsed time in days, hours, minutes, and seconds.

GetTimeCardTotal() Sample Function

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

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 four records, and then close the table:

         8:15
         7:37
         8:12
         8:03
   

3. Create a module and type the following line in the Declarations section if it's not already there:

         Option Explicit
   

4. 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
   
   

5. 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.

REFERENCES

For more information about calculating date/time values, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q130514
   TITLE     : ACC: Storing, Calculating, and Comparing Date/Time Data