Re: How to calculate time using this function

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Okay, Jeff I did that already but it only shows the time for the first record
and I might have a total of 1000 records and it shows 1 minutes.




"Jeff Boyce" wrote:

Jen

If your function returns the value(s) you need, call it from a query by
adding it as a new field in the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jen" <Jen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:99170F02-7DD5-4C4F-A0A1-3A42984D01D6@xxxxxxxxxxxxxxxx
The following is a module I use to calculate time. I use the following
string in my reports. But now I need to know how to Add all the calculated
time to show manpower per report not just a record.

String:
=IIf([EndDate]>0,ElapsedTimeString([StartDate],[EndDate]),0)

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





.

Relevant Pages

  • Re: Create Table SQL not working
    ... Tim & Jeff seemed to be so ... > concatenating the Number (converted to a string) to the end of a constant ...
    (microsoft.public.access.tablesdbdesign)
  • Trouble accessing db while using ASP.Net
    ... When I try to open the connection ... "Exception Message: ... ASP.LoginPage_aspx.CustomAuthenticate(String username, String ... User name: jeff" ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Make Multiple Tables via Query
    ... Dim strTableName As String ... Dim strExcelFileName As String ... "Jeff Boyce" wrote: ...
    (microsoft.public.access.queries)
  • Re: GAS
    ... What did you play, Jeff? ... rosewood at a used price that has almost everything I'd been selling myself ... I'd been working myself up to a $1.5k to $2k nice OM or similar six string ...
    (rec.music.makers.guitar.acoustic)
  • Re: Standard FileOpen Window
    ... "Jeff Jones" wrote in message ... > Private Type OPENFILENAME ... > lpstrCustomFilter As String ... > FileName = Trim$ ...
    (microsoft.public.project.vba)