Share via


Constructing Modern Time Elapsed Strings in Access 2007

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

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

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.

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

  1. Click the Microsoft Office Button, click Open, and then open the database where you want to insert the functions.

  2. On the Microsoft Office Fluent Ribbon, click Database Tools.

  3. On the Macro tab, click Visual Basic.

  4. On the Insert menu, click Module.

  5. If they are not already there, type the following two lines of code.

    Option Compare Database
    Option Explicit
    
  6. 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.

  7. On the File menu, click Save.

  8. Type a module name, such as ElapsedTime, and then press ENTER.

  9. On the Debug menu, click Compile.

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

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.

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: