Calculating Times on a report

[GDCMike]

Hello,

I have a database which registers when a ticket is opened and completed using the now() function.

What I would like to do is to report how long each ticket takes to be resolved (in days hours:minutes)

The fields I have are
"Time"
"Complete Time"

I've tired using datediff - but I must be doing something wrong because it always reports the number of days in the month if the ticket was resolved on the same day!

If anyone can help it would be gratefully appreciated.

Thanks,

Mike Healy.

 

[lupins46]

Get the difference in seconds and then divide by 60 etc to get the formatted time.

Function GetTime(vStart As Date, vEnd As Date) As String

Dim s, dd, hh, mm
s = DateDiff("s", vStart, vEnd)
dd = s \ 86400 ' get days
s = s - dd * 86400
hh = s \ 3600 ' get hours
s = s - hh * 3600
mm = s \ 60 'get minutes
s = s - mm * 60 ' get seconds

GetTime = Format(dd, "00") & " " & Format(hh, "00") & ":" & Format(mm, "00") & ":" & Format(s, "00")

End Function

Usage:
?gettime("2004/03/03 8:50:00","2004/03/05 02:45:00")
01 17:55:00

 

[scriverb]

You should be able to do a straight subtraction of the two time controls:

=Me![Time] - Me![TimeCompleted]

Copy and paste the above code into the Control Source in your report. This does require the two controls to be named as indicated. Use the Short Time format for the calculated control.

Post back if you experience additional problems.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[GDCMike]

Thanks for the replys both of you, but...

I'm Afraid I'm too stoopid to know where to use the 1st one, and I cant get the second one to work - if the "ME!" bits are in hte result is just "#Name" and without, although the number of hours is correct BUT, the number of days is always the number of days in a month -1 if the ticket was opened and resolved on the same day (eg for a ticket opened and closed on march 18th 2004 the value is 30). If then I change the date so the ticket was opened on the 18th and clsoed on the 19th, It would then say "29" for the days value.

I have tried changing the formatting and using two fields but to no avail.

I would be grateful of any more ideas.

Thanks,

Mike Healy

 

[scriverb]

GDCMike: I misread your posting concerning the need for days. Using the code provided by lupins46 you need to put it into a database module. lupins46: Hope you don't mind me helping out here with your code. Then you can call this function from a query or other VBA code to get the numbers that you requested.

Copy this code and paste it into a database module:

Function GetTime(vStart As Date, vEnd As Date) As String
Dim s, dd, hh, mm
s = DateDiff("s", vStart, vEnd)
dd = s \ 86400 ' get days
s = s - dd * 86400
hh = s \ 3600 ' get hours
s = s - hh * 3600
mm = s \ 60 'get minutes
s = s - mm * 60 ' get seconds
GetTime = Format(dd, "00") & " " & Format(hh, "00") & ":" & Format(mm, "00") & ":" & Format(s, "00")
End Function

Now the following is an example of a query where you can make a call to the above function and get the ElapsedTime string in the format requested:

SELECT A.[Time], A.[Completed Time], GetTime(A.[Time],A.[Completed Time]) AS ElapsedTime(dd hh:mm:ss)
FROM [red]YourTableName[/red] as A;

You can also make a similar call to this function in a form or report and display the results in a control. If you have more questions post back and lupins46 or myself can help you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[lupins46]

To use the function I posted you need to create a new standard module and paste the code into it.
You can then use it in a query to create a new column:

MyTime:getetime(startfield,endfield)

The alternative answer that has been suggested can only work if the times are in the same day.