TimeElapsed function gives #error on new record only

[MsLady]

I have attached the db below. It's a smalldb with just one form

I have a function called ElapsedTimeString that produces the Hours, minutes, and seconds time elapsed between two time fields (my function module is below).

I have a continous form that i use for dataentry.
the fields are Username, startime, endtime, and an unbound control called txtTotalTimeSpent just to display the total time to the user.
The control source on txtTotalTimeSpent is:

=ElapsedTimeString([timeStart],[timeStop]))) 

'I tried this also, still no luck
=IIf(IsNull([timeStart]) Or IsNull([timeStop]),[totalTimeSpent].Value="",(ElapsedTimeString([timeStart],[timeStop])))

Now whenever there is a new record, "#error" automatically displayes in the textbox (txtTotalTimeSpent).
I have attached the file, please how do i get rid of this #error. I have tried error handling to do nohting no luck. I have tried everything ican think of. Can anyone help give it a try?

Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
'**********************************************************************************************
' Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
' Returns the time elapsed between a starting Date/Time and an ending
' Date/Time formatted as a string that looks like this:
' "10 days, 20 hours, 30 minutes, 40 seconds".
'**********************************************************************************************
Dim interval As Double, str As String, days As Variant
Dim hours As String, minutes As String, seconds As String

  If IsNull(dateTimeStart) = True Or _
    IsNull(dateTimeEnd) = True Then
    ElapsedTimeString = 0
    Exit Function
  End If

  interval = dateTimeEnd - dateTimeStart
  days = Fix(CSng(interval))
  hours = Format(interval, "h")
  minutes = Format(interval, "n")
  seconds = Format(interval, "s")

' Days part of the string
  str = IIf(days = 0, "", _
    IIf(days = 1, days & " Day", days & " Days"))
  str = str & IIf(days = 0, "", _
    IIf(hours & minutes & seconds <> "000", ", ", " "))

' Hours part of the string
  str = str & IIf(hours = "0", "", _
    IIf(hours = "1", hours & " Hour", hours & " Hours"))
  str = str & IIf(hours = "0", "", _
    IIf(minutes & seconds <> "00", ", ", " "))

' Minutes part of the string
  str = str & IIf(minutes = "0", "", _
    IIf(minutes = "1", minutes & " Minute", minutes & " Minutes"))
  str = str & IIf(minutes = "0", "", IIf(seconds <> "0", ", ", " "))

' Seconds part of the string
  str = str & IIf(seconds = "0", "", _
    IIf(seconds = "1", seconds & " Second", seconds & " Seconds"))
  ElapsedTimeString = IIf(str = "", "0", str)

End Function

 

[RuralGuy]

Hi there!
Fixed the defective formula in the TotalTimeSpent textbox.

 

[MsLady]

Hi there!
Fixed the defective formula in the TotalTimeSpent textbox.

wow!

*does a little break dance
what would i do wihtout you?
I've been battling that error for hours
thanks quality guru

 

[MsLady]

that extra "[totalTimeSpent].Value="" i had in there was evil ... LOL
i was close

 

[RuralGuy]

*Very* close. Have a cigar!

 

[MsLady]

Hi Rural guy,
Ive come again
I am trying to fillin the Grandtotal of all the timeElapsed to show up in the format of Hours, Minutes, seconds... into the txtGrandTotalTime (box at the very top right) can you help me take a look or work ur magic on it?

I thought simply making the control souce =Sum([txtGrandTotalTime]) should work...but obviously not..

Then...i have that invisible txtTotal with control source "=DateDiff("n",[timeStart],[timeStop])" ... then i made txtGrandTotalTime Control source "=ElapsedTimeString(0,Sum([txtTotal]))" ... but no... argh

Please help

 

[RuralGuy]

Hi MsLady,
Did I get close? If not, does it give you a couple of ideas? I've got more if you need them.

 

[Kempes]

Hello again,

Try this in the control Source. (Works a treat as a visual aid on the form)

=Format(Sum([Timestop]-[timestart]),"hh") & " Hours" & " " & Format(Sum([Timestop]-[timestart]),"nn") & " Mins" & " " & Format(Sum([Timestop]-[timestart]),"ss") & " Secs"

 

[Kempes]

Sorry RG, didn't notice your fix attached.

Good work mate!

 

[MsLady]

Hi MsLady,
Did I get close? If not, does it give you a couple of ideas? I've got more if you need them.

Hello again,

Try this in the control Source. (Works a treat as a visual aid on the form)

=Format(Sum([Timestop]-[timestart]),"hh") & " Hours" & " " & Format(Sum([Timestop]-[timestart]),"nn") & " Mins" & " " & Format(Sum([Timestop]-[timestart]),"ss") & " Secs"

I love you guys!!!

Both solutions work accurately.
thanx alot


RG, even tho it works. u mind if i ask. how you got [elapsed] in there. I don't have a function called "elapsed" and u didn't use my [elapsedTimeString]

 

[MsLady]

hmn....still looking
so i guess u guys kicked my ElapsedTimeString aside

RG, still can't find ur secret function ottay..!

=IIf(IsNull([timeStart]) Or IsNull([timeStop]),"",IIf(Format([Elapsed],"h")="0",Format([Elapsed],"n") & " mins",Format([Elapsed],"h") & " hours " & Format([Elapsed],"n") & " mins"))

 

[MsLady]

LOL... found it!!!
qryAlltime

That was brilliant

THANKS GUYS!!!