Help with elapsed time function in form

[will1128]

I have a table with a star time and end time with values entered in like this
Start Time EndTime
10/23/2008 6:15:51 PM 3/25/2009 9:27:40 AM

They are of date/time type and a general date format string.
If I try to use DateDiff(endtime-start time) I get an #Name error.

When I've tried to used functions found on MS, such as GetElapsedTime() or Elapsed Time I get an#Error, but not details as to my cause.

How can I pass in the entire Date/Time field and have it return the elapsed time in hours :MM:ss: without separating the column into day and time? So that when I subtract those two values I get something like 28:07:39:10?

 

[boblarson]

You need to look up the syntax on DateDiff:

DateDiff([date or time element as string], [Starting Date/time], [Ending Date/time])

So, for example if I want days difference in a query it would be:

DateDiff("d",[StartDateFieldInBrackets], [EndDateFieldNameInBrackets])

on a form it would be slightly different (using the name of the controls):

Something = DateDiff("d", Me.StartDate, Me.EndDate)

Does that help?

 

[jdraw]

I have a table with a star time and end time with values entered in like this
Start Time EndTime
10/23/2008 6:15:51 PM 3/25/2009 9:27:40 AM

They are of date/time type and a general date format string.
If I try to use DateDiff(endtime-start time) I get an #Name error.

When I've tried to used functions found on MS, such as GetElapsedTime() or Elapsed Time I get an#Error, but not details as to my cause.

How can I pass in the entire Date/Time field and have it return the elapsed time in hours :MM:ss: without separating the column into day and time? So that when I subtract those two values I get something like 28:07:39:10?

In addition to Bob's comments, be careful with spaces in field names.
You'll need to use [Start time] with these square brackets.

 

[will1128]

I wish it did, but perhaps I still don't understand the syntax.

The formula I'm trying to use in the Control Source for a form is =DateDiff("d",[PR]![End Time],[PR]![Start Time])

BUT DateDiff takes in the following syntax

DateDiff («interval», «date1», «date2», «firstweekday», «firstweek»)

Do I really need the firstweekday and firstweek?

 

[will1128]

I had the syntax wrong, [PR.End] vs. the exclamation point. It works for days, but how can I do it so it will give me the total elapsed time?

 

[raskew]

Hi -

The following will return elapsed time in weeks, days, hours, minutes, seconds as specified by the operator. It shouldn't be difficult to modify the display to meet your needs.

Public Function DtDiff(sdate As Date, edate As Date, Optional pItems As String = "wdhns") As String
'*******************************************
'Purpose:   Compute date difference in weeks, days, hours, minutes, 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 strChar  As String
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

DtDiff = Trim(strKeep)

End Function

HTH - Bob

 

[will1128]

Thank you, but I have little to NO experience programming in VBA. So sadly, I've copied and pasted the code you've given me, which I understand, but can't seem to undestand how to use as a control source, my ultimate goal.

I've used your function and tried to use a control source displayed as
=DtDiff([PR.Start Time],[PR.End Time],"dhns")

My confusion lies in the #date# you have as input a date with ##,

How am I able to enter a field that is already a date to begin with?
I've tried using
=DtDiff(#[PR.Start Time]#,#[PR.End Time]#,"dhns")

and that doesn't do the trick.

 

[raskew]

Try changing the first line:
Public Function DtDiff(sdate As Date, edate As Date, Optional pItems As String = "wdhns") As String

to

Public Function DtDiff(ByVal sdate As Date, ByVal edate As Date, Optional pItems As String = "wdhns") As String

Do not place ## around your control names as you illustrated in post #7.

Bob

 

[will1128]

Bob,

Can you tell me what the ByVal syntax does? And I have removed the # symbols, but I don't understand what's is happening.

When I've tried to use the formula =DtDiff([PR.Start Time],[PR.End Time],"dhns") or even =DtDiff ( [PR]![Start Time] , [PR]![End Time] , "dhns") I continually receive #Name? as an error.

As far as I understand it, MS thinks I don't have valid input, but my table definitely has a Start Time and an End Time as a General Date mm/dd/yyyy H:MM:SS AM/PM.

Would you please help me understand what's going on?

Thanks, Kimberly

 

[raskew]

Hi -

Is it possible for you to post an example db?

Bob

 

[will1128]

I've attached what I was working with. The name of the form is PR_Form.

It's the Control Source of the Elapsed Time Text Box I'm trying to get to update.

Thanks for all your help!!!

 

[raskew]

Just unzipped your ap. Haven't had time to get into it yet, but the one thing I note is that you've named your modules with the same name as the sub/function within.

That is an absolute no-go. Go back and rename your modules Mod1, Mod2, Mod3. Incidentally, you don't need a separate module for each sub/function. All three of the procedures can (and probably should) be loaded into one module.

Bob

 

[will1128]

Renaming the modules worked! Thanks, but could someone tell me why?