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