Oops.
Throw a 'Dim strChar as String' in there.
Got lost in the copy/paste operation.
Bob
Give this a try. Hopefully it'll be self-explanatory.
****************************************************************************************
Public Function DtDiff(sdate As Date, edate As Date, Optional pItems As
String = "wdhns") As String
'*******************************************
're:
http://www.accessmonster.com/Uwe/Forum.aspx/access-modulesdaovba/3004...
'Purpose: Compute date difference in weeks, days, hours, minutes, seconds
' seconds as specified by operator.
'Coded by: raskew
'Inputs: 1) ? dtdiff(#2008-May-01 12:00:43 PM#, #2008-Jun-02 5:39:11 PM#,
"wdhns")
' 2) ? dtdiff(#2008-May-01 12:00:43 PM#, #2008-Jun-02 5:39:11 PM#,
"dhn")
'Output: 1) 4 weeks 4 days 5 hours 38 minutes 28 seconds
' 2) 32 days 5 hours 38 minutes
'*******************************************
Dim timehold, xweeks, xdays, xmins, xhrs, xsecs
Dim strHold As String
Dim strKeep As String
Dim n As Integer
'calculate all date/time intervals
timehold = DateDiff("s", sdate, edate)
xweeks = timehold \ 7 * 86400
xdays = timehold \ 86400 - (xweeks * 7)
xhrs = timehold \ 3600 - (xdays * 24) - (xweeks * 7 * 24)
xmins = timehold \ 60 - (xhrs * 60) - (xdays * 24 * 60) - (xweeks * 7 * 24 *
60)
xsecs = timehold Mod 60
'adjust for intervals selected
strHold = "wdhns"
For n = 1 To 5
strChar = Mid(strHold, n, 1)
If InStr(pItems, strChar) = 0 Then
If strChar = "w" Then
xdays = xdays + (xweeks * 7)
xweeks = 0
ElseIf strChar = "d" Then
xhrs = xhrs + (xdays * 24)
xdays = 0
ElseIf strChar = "h" Then
xmins = xmins + (xhrs * 60)
xhrs = 0
ElseIf strChar = "n" Then
xsecs = xsecs + (xmins * 60)
xmins = 0
ElseIf strChar = "s" Then
xsecs = 0
End If
End If
Next n
'Format Output
For n = 1 To Len(pItems)
strChar = Mid(pItems, n, 1)
If strChar = "w" Then
strKeep = strKeep & xweeks & " week" & IIf(xweeks <> 1, "s", "") & " "
ElseIf strChar = "d" Then
strKeep = strKeep & xdays & " day" & IIf(xdays <> 1, "s", "") & " "
ElseIf strChar = "h" Then
strKeep = strKeep & xhrs & " hour" & IIf(xhrs <> 1, "s", "") & " "
ElseIf strChar = "n" Then
strKeep = strKeep & xmins & " minute" & IIf(xmins <> 1, "s", "") & " "
ElseIf strChar = "s" Then
strKeep = strKeep & xsecs & " second" & IIf(xsecs <> 1, "s", "") & " "
End If
Next n
End Function
****************************************************************************************
additionally the users have realised that they can put in the three
letters
[quoted text clipped - 10 lines]
language as their version of Windows so that even if one user uses German
and another user uses French against the same back-end, it should work.
Hello All
i have worked out this problem using the diff2date function i found
through dougs suggestion.
however i have something i want to share with the community that i
added to the function. it wasnt hard to do, and i bet others have
thought of it as well, but i still feel that it is worth sharing.
i needed a way to show just the largest interval out of the diff2date
function, so i added it to the function. here is the completed code:
Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As
Date, Optional ShowZero As Boolean = False, Optional LargestInterval
As Boolean = False) As Variant
'Author: ) Copyright 2001 Pacific Database Pty Limited
' Graham R Seach MCP MVP (e-mail address removed)
' Phone: +61 2 9872 9594 Fax: +61 2 9872 9593
' This code is freeware. Enjoy...
' (*) Amendments suggested by Douglas J. Steele MVP
' (*) Largest Interval Function by Alan R Tonn
'
'Description: This function calculates the number of years,
' months, days, hours, minutes and seconds between
' two dates, as elapsed time.
'
'Inputs: Interval: Intervals to be displayed (a string)
' Date1: The lower date (see below)
' Date2: The higher date (see below)
' ShowZero: Boolean to select showing zero elements
'
'Outputs: On error: Null
' On no error: Variant containing the number of years,
' months, days, hours, minutes & seconds between
' the two dates, depending on the display interval
' selected.
' If Date1 is greater than Date2, the result will
' be a negative value.
' The function compensates for the lack of any intervals
' not listed. For example, if Interval lists "m", but
' not "y", the function adds the value of the year
' component to the month component.
' If ShowZero is True, and an output element is zero, it
' is displayed. However, if ShowZero is False or
' omitted, no zero-value elements are displayed.
' For example, with ShowZero = False, Interval = "ym",
' elements = 0 & 1 respectively, the output string
' will be "1 month" - not "0 years 1 month".
On Error GoTo Err_Diff2Dates
Dim booCalcYears As Boolean
Dim booCalcMonths As Boolean
Dim booCalcDays As Boolean
Dim booCalcHours As Boolean
Dim booCalcMinutes As Boolean
Dim booCalcSeconds As Boolean
Dim booSwapped As Boolean
Dim dtTemp As Date
Dim intCounter As Integer
Dim lngDiffYears As Long
Dim lngDiffMonths As Long
Dim lngDiffDays As Long
Dim lngDiffHours As Long
Dim lngDiffMinutes As Long
Dim lngDiffSeconds As Long
Dim varTemp As Variant
Const INTERVALS As String = "dmyhns"
'Check that Interval contains only valid characters
Interval = LCase$(Interval)
For intCounter = 1 To Len(Interval)
If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then
Exit Function
End If
Next intCounter
'Check that valid dates have been entered
If Not (IsDate(Date1)) Then Exit Function
If Not (IsDate(Date2)) Then Exit Function
'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
If Date1 > Date2 Then
dtTemp = Date1
Date1 = Date2
Date2 = dtTemp
booSwapped = True
End If
Diff2Dates = Null
varTemp = Null
'What intervals are supplied
booCalcYears = (InStr(1, Interval, "y") > 0)
booCalcMonths = (InStr(1, Interval, "m") > 0)
booCalcDays = (InStr(1, Interval, "d") > 0)
booCalcHours = (InStr(1, Interval, "h") > 0)
booCalcMinutes = (InStr(1, Interval, "n") > 0)
booCalcSeconds = (InStr(1, Interval, "s") > 0)
'Get the cumulative differences
If booCalcYears Then
lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - IIf(Format
$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)
Date1 = DateAdd("yyyy", lngDiffYears, Date1)
End If
If booCalcMonths Then
lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - IIf(Format$
(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)
Date1 = DateAdd("m", lngDiffMonths, Date1)
End If
If booCalcDays Then
lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - IIf(Format$
(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
Date1 = DateAdd("d", lngDiffDays, Date1)
End If
If booCalcHours Then
lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - IIf(Format$
(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
Date1 = DateAdd("h", lngDiffHours, Date1)
End If
If booCalcMinutes Then
lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - IIf(Format$
(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
Date1 = DateAdd("n", lngDiffMinutes, Date1)
End If
If booCalcSeconds Then
lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
Date1 = DateAdd("s", lngDiffSeconds, Date1)
End If
'this section connects all the parts together. this is responsible
for giving the string of time intervals.
If LargestInterval = False Then
If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years",
" year")
End If
If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
If booCalcMonths Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") &
lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")
End If
End If
If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
If booCalcDays Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") &
lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
End If
End If
If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
If booCalcHours Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") &
lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
End If
End If
If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
If booCalcMinutes Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") &
lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")
End If
End If
If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
If booCalcSeconds Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") &
lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")
End If
End If
Else
If lngDiffYears >= 1 Then
varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years",
" year")
ElseIf lngDiffMonths >= 1 Then
varTemp = lngDiffMonths & IIf(lngDiffMonths <> 1, "
months", " month")
ElseIf lngDiffDays >= 1 Then
varTemp = lngDiffDays & IIf(lngDiffDays <> 1, " days", "
day")
ElseIf lngDiffHours >= 1 Then
varTemp = lngDiffHours & IIf(lngDiffHours <> 1, " hours",
" hour")
ElseIf lngDiffMinutes >= 1 Then
varTemp = lngDiffMinutes & IIf(lngDiffMinutes <> 1, "
minutes", " minute")
Else
varTemp = lngDiffSeconds & IIf(lngDiffSeconds <> 1, "
seconds", " second")
End If
End If
If booSwapped Then
varTemp = "-" & varTemp
End If
Diff2Dates = Trim$(varTemp)
End_Diff2Dates:
Exit Function
Err_Diff2Dates:
Resume End_Diff2Dates
End Function
i am now thinking of a way to allow the user to chose how the text for
the intervals should look.
