6 out of 6 rated this helpful - Rate this topic

Constructing Modern Time Elapsed Strings in Access 2007

Office 2007

Summary: Learn how to use Microsoft Office Access 2007 to display the time elapsed between the current date and another date. (5 printed pages)

Kerry Westphal, Microsoft Corporation

March 2009

Applies to: 2007 Microsoft Office system, Microsoft Office Access 2007

Contents

Overview
How It Works
Creating the User-Defined Function
Download the Sample Database
Conclusion
Additional Resources

Overview

Many Web 2.0 applications are designed to make it easy to vizualize complex data. I found myself recently challenged with this task while working on a project where I wanted to display on a report to show the time elapsed between the current date and another date. Some example scenarios could include how much time has elapsed since a user profile was updated, the time that remains until taxes are due, or how long a library book was checked out. I did not merely want to show the hours or even days elapsed, but something more in sync with the way I want the information given to me─specifically, that when dates are closer to the current date and time that they are represented exactly, and dates and times that are farther away are shown generally. I wrote the ElapsedTime user-defined function to perform this task. The function can be used in a query to obtain a string that represents the time elapsed. The string returned is either specific or general depending on the length of time elapsed. For example, if the date is close to the current date, it appears as "In 12 hours, 27 minutes". If the date was long ago, it appears as, "A year ago". The following screen shot shows the results of the ElapsedTime function when it is used to track items in a calendar.

Figure 1. Report showing modern elapsed time string

How It Works

The ElapsedTime function does the work. Call ElapsedTime from a form, report, or query to get a string that shows the time elapsed between the date that you pass the function and the current date. Pass ElapsedTime a date/time value as its only argument and the rest is completed for you.

Copy

Public Function ElapsedTime(dateTimeStart As Date) As String
'*************************************************************
' Function ElapsedTime(dateTimeStart As Date) As String
' Returns the time elapsed from today in a display string like,
' "In 12 hours, 41 minutes"
'*************************************************************
    On Error GoTo ElapsedTime_Error
    Dim result As String
    Dim years As Double
    Dim month As Double
    Dim days As Double
    Dim weeks As Double
    Dim hours As Double
    Dim minutes As Double

    If IsNull(dateTimeStart) = True Then Exit Function

    years = DateDiff("yyyy", Now(), dateTimeStart)
    month = DateDiff("m", Now(), dateTimeStart)
    days = DateDiff("d", Now(), dateTimeStart)
    weeks = DateDiff("ww", Now(), dateTimeStart)
    hours = DateDiff("h", Now(), dateTimeStart)
    minutes = DateDiff("n", Now(), dateTimeStart)
  
    Select Case years
        Case Is = 1
            result = "Next year"
        Case Is > 1
            result = "In " & years & " years"
        Case Is = -1
            result = "Last Year"
        Case Is < -1
            result = Abs(years) & " years ago"
    End Select
   
    Select Case month
        Case 2 To 11
            result = "In " & month & " months"
        Case Is = 1
            result = "This month"
        Case Is = -1
            result = "Last month"
        Case -11 To -2
            result = Abs(month) & " months ago"
    End Select

    Select Case days
        Case 2 To 6
            result = "In " & days & " days"
        Case Is = 1
            result = "Tomorrow"
        Case Is = -1
            result = "Yesterday"
        Case -6 To -2
            result = Abs(days) & " days ago"
    End Select
   
    Select Case weeks
        Case 2 To 5
            result = "In " & weeks & " weeks"
        Case Is = 1
            result = "Next week"
        Case Is = -1
            result = "Last week"
        Case -5 To -2
            result = Abs(weeks) & " weeks ago"
    End Select

    Select Case hours
        Case Is = 1
            Select Case minutes - (Int(minutes / 60) * 60)
                Case Is = 0
                    result = "In an hour"
                Case Is = 1
                    result = "In an hour and one minute"
                Case Is = -1
                    result = "In an hour and one minute"
                Case 2 To 59
                    result = "In an hour and " & _
                        minutes - (Int(minutes / 60) * 60) & " minutes"
                Case 60
                    result = "In an hour"
                Case -59 To -2
                    result = "In an hour and " & _
                        minutes - (Int(minutes / 60) * 60) & " minutes"
                Case -60
                    result = "In an hour"
            End Select
        Case 2 To 23
            Select Case minutes - (Int(minutes / 60) * 60)
                Case Is = 1
                    result = "In " & Int(minutes / 60) & _
                        " hours and one minute"
                Case Is = 0
                    result = "In " & Int(minutes / 60) & " hours"
                Case 2 To 59
                    result = "In " & Int(minutes / 60) & " hours, " & _
                        minutes - (Int(minutes / 60) * 60) & " minutes"
                Case Is = -1
                    result = "In " & Int(minutes / 60) & _
                        " hours and one minute"
                Case -59 To -2
                    result = "In " & Int(minutes / 60) & " hours, " & _
                        minutes - (Int(minutes / 60) * 60) & " minutes"
                Case Is = 60
                    result = "In " & Int(minutes / 60) & " hours"
                Case Is = -60
                    result = "In " & Int(minutes / 60) & " hours"
            End Select
        Case Is = -1
            Select Case (Int(minutes / 60) * 60) - minutes + 60
                Case Is = 0
                    result = "An hour ago"
                Case Is = 1
                    result = "An hour and 1 minute ago"
                Case 2 To 59
                    result = "An hour ago and " & _
                        (Int(minutes / 60) * 60) - minutes + 60 & _
                        " minutes ago"
                Case 60
                    result = "An hour ago"
                Case Is = -1
                    result = "An hour and 1 minute ago"
                Case -59 To -2
                    result = "An hour ago and " & _
                        (Int(minutes / 60) * 60) - minutes + 60 & _
                        " minutes ago"
                Case -60
                    result = "An hour ago"
            End Select
        Case -23 To -2
            Select Case (Int(minutes / 60) * 60) - minutes + 60
                Case Is = 0
                    result = Abs(Int(minutes / 60) + 1) & " hours ago"
                Case Is = 1
                    result = Abs(Int(minutes / 60) + 1) & _
                        " hours and one minute ago"
                Case 2 To 59
                    result = Abs(Int(minutes / 60) + 1) & " hours, " _
                    & (Int(minutes / 60) * 60) - minutes + 60 & _
                    " minutes ago"
                Case 60
                    result = Abs(Int(minutes / 60)) & " hours ago"
                Case Is = -1
                    result = Abs(Int(minutes / 60) + 1) & _
                        " hours and one minute ago"
                Case -59 To -2
                    result = Abs(Int(minutes / 60) + 1) & _
                        " hours, " & _
                        (Int(minutes / 60) * 60) - minutes + 60 & _
                        " minutes ago"
                Case -60
                    result = Abs(Int(minutes / 60) + 1) & " hours ago"
            End Select
    End Select
   
    Select Case minutes
        Case 2 To 59
            result = "In " & minutes & " minutes "
        Case Is = 1
            result = "In 1 minute"
        Case Is = 0
            result = "Now"
        Case Is = -1
            result = "A minute ago"
        Case -59 To -2
            result = Abs(minutes) & " minutes ago"
    End Select
 
    ElapsedTime = result

ElapsedTime_Exit:
    Exit Function
    
ElapsedTime_Error:
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "ElapsedTime"
    Resume ElapsedTime_Exit

End Function

The ElapsedTime function returns a description of the time elapsed between the date specified and the current date. You can pass it a date from the past or a date in the future. ElapsedTime then uses the DateDiff function to compute the interval in years, month, days, weeks, hours, and minutes between now and the date passed to ElapsedTime. The remainder of the work consists of examining each of these intervals to build the correct result string. This can be a tedious task, because you must use different text for intervals of zero, one, greater than one, and positive (future), or negative (past) values. For example, when the number of minutes is greater than one, the text should say "minutes". However, when the number of minutes is exactly 1, the text should use the singular form, which is "minute". This information is important and the corresponding lines of code make up most of the function.

Creating the User-Defined Function

To download a sample database that includes the user-defined function presented here, see the ElapsedTime Sample Database for Access 2007. This database contains the TimeElapsed module that contains the ElapsedTime function. To create the user-defined function, start Access 2007 and use the following procedure.

To create the user-defined function

Click the Microsoft Office Button, click Open, and then open the database where you want to insert the functions.
On the Microsoft Office Fluent Ribbon, click Database Tools.
On the Macro tab, click Visual Basic.
On the Insert menu, click Module.
If they are not already there, type the following two lines of code.
VB
Copy
Option Compare Database Option Explicit
Type the ElapsedTime function exactly as it appears in the previous code example. If you downloaded the sample database, copy and paste the function from the TimeElapsed module.
On the File menu, click Save.
Type a module name, such as ElapsedTime, and then press ENTER.
On the Debug menu, click Compile.
On the File menu, click Close and Return to Microsoft Office Access.

After you enter the function into your database, you can use it in Access objects such as queries, forms, and reports.

Download the Sample Database

Conclusion

This article discussed the ElapsedTime user-defined function that you can use in a Microsoft Office Access 2007 database applications. A sample database is provided for you to download. You can use the ElapsedTime user-defined function in your own Queries, Forms, and Reports to provide a more readable description of the time elapsed between a given date and time value and the current date and time.

Additional Resources

For more information about Access 2007 and the Access 2007 Runtime, see the following resources:

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Content Add

FAQ

Get Time Elapsed in VB 6.0

The below ElapsedTime() will return elapsed time between two Time-Stamps, either in elapsed seconds or Days:Hours:Minutes:Seconds or Hours:Minutes:Seconds.

Usage:
Call ElapsedTime("29/09/2011 08:55:59 PM", "29/09/2011 09:01:57 PM", False, False)

Output:
0 Hrs. 5 Min. 58 Sec.

VB 6.0 Source:
Public Function ElapsedTime(ByVal DateTime1 As String, ByVal DateTime2 As String, Optional ByVal InDays As Boolean = False, Optional ByVal InElapsedSeconds As Boolean = False) As String

    'DateDifference in Hours:Minutes:Seconds
    'Input: "dd/mm/yyyy hh:mm:ss AM" [or PM]
    On Error GoTo hErr

    Dim beginDateTime     As Date
    Dim endDateTime       As Date
    Dim lngDays           As Long
    Dim lngHrs            As Long
    Dim lngMin            As Long
    Dim lngSec            As Long
    Dim lngElapsedSeconds As Long

    'Express in proper Date
    beginDateTime = CDate(DateTime1)
    endDateTime = CDate(DateTime2)

    'Total Seconds Elapsed
    lngElapsedSeconds = DateTime.DateDiff("s", beginDateTime, endDateTime)

    If InElapsedSeconds = True Then
        'return total-seconds-elapsed
        ElapsedTime = lngElapsedSeconds
        Exit Function
    End If

    'Calculation [express Days Hours Minutes Seconds]
    If InDays = True Then
        '[express Days]
        lngDays = lngElapsedSeconds \ (86400)
        lngElapsedSeconds = lngElapsedSeconds Mod (86400)
    End If

    '[express Hours Minutes Seconds]
    lngHrs = lngElapsedSeconds \ (3600)
    lngElapsedSeconds = lngElapsedSeconds Mod (3600)

    lngMin = lngElapsedSeconds \ (60)
    lngElapsedSeconds = lngElapsedSeconds Mod (60)

    lngSec = lngElapsedSeconds

    'Output
    If InDays = True Then
        ElapsedTime = lngDays & " Days " & lngHrs & " Hrs. " & lngMin & " Min. " & lngSec & " Sec."
    Else
        ElapsedTime = lngHrs & " Hrs. " & lngMin & " Min. " & lngSec & " Sec."
    End If

    Exit Function
hErr:
    'on any error [SSR]
    ElapsedTime = vbNullString
End Function

History

10/23/2011
Sivarajendra

dateTimeStart should be Variant type

dateTimeStart should be Variant type, else you can get #Error when using this function in a query with null in date field.
Like this:

Public Function ElapsedTime(dateTimeStart As Variant) As String
'*************************************************************
' Function ElapsedTime(dateTimeStart As Date) As String
' Returns the time elapsed from today in a display string like,
' "In 12 hours, 41 minutes"
'*************************************************************
On Error GoTo ElapsedTime_Error
Dim result As String
Dim years As Double
Dim month As Double
Dim days As Double
Dim weeks As Double
Dim hours As Double
Dim minutes As Double

If Not IsDate(dateTimeStart) = True Then Exit Function

History

5/28/2009
Alex Dybenko

Last year and next year aren't grammatically consistent

Last year being 2008 and next year being 2010. In my opinion the lines of code

Select Case years
Case Is = 1
result = "Next year"
Case Is > 1
result = "In " & years & " years"
Case Is = -1
result = "Last Year"
Case Is < -1
result = Abs(years) & " years ago"
End Select

Should be changed to

Select Case years
Case Is = 1
result = "In a year"
Case Is > 1
result = "In " & years & " years"
Case Is = -1
result = "A year ago"
Case Is < -1
result = Abs(years) & " years ago"
End Select

Sorry about the lack of indenting but leading tabs and spaces are removed.

History

3/26/2009
Tony Toews - Access MVP

3/27/2009
Tony Toews - Access MVP