Date, Time Calculations

C

Chuck216

Hi

I have a question hopefully somebody can answer. I’m not real good with code
I just kind of stumble my way around. Anyway I got this function from
Microsoft’s website in there reference database for elapse time and date
functions. I’m using this code in a query to get the down time for
attractions and it works just fine for giving me the total down time for a
particular incident. My problem is I want to be able to do a report and have
the total down time for all incidents on a particular attraction. Is there
anyway to get that total time?

Thanks in advance for any help
Chuck



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 Exit Function

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
 
V

Van T. Dinh

Unfortunately, the function you posted is not really suitable for what you
are trying to do ...

The function name "ElapsedTimeString" implies that it returns a String which
is not suitable for further numerical manipulations. In your case, you need
to add the durantions of down-times for particular "attraction", e.g. you
need to perform numerical manipulations.

What you wanted probably can be done using a Total Query but we have no
details of your set-up to suggest. Post the relevant Table Structure /
Fields of your Database and precise what you need (e.g. any date restriction
on the total down-times, etc ...)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top