Try these steps:
1) Add these modules to your database:
Code:
Public Function HoursAndMinutes(interval As Variant) As String
'*************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'*************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function
hours = Int(CSng(interval * 24))
' 1440 = 24 hrs * 60 mins
totalminutes = Int(CSng(interval * 1440))
minutes = totalminutes Mod 60
' 86400 = 1440 * 60 secs
totalseconds = Int(CSng(interval * 86400))
seconds = totalseconds Mod 60
' Round up the minutes and adjust hours
If seconds > 30 Then minutes = minutes + 1
If minutes > 59 Then hours = hours + 1: minutes = 0
HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function
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
2) Use this as your SQL for your query
Code:
SELECT tbl2.DateTimereturned, tbl2.DateTimecalled, ElapsedTimeString([DateTimeReturned],[DateTimecalled]) AS TAT
FROM tblIMDatabase2
WHERE tbl2.Received = True
GROUP BY tbl2.DateTimecalled, tbl2.DateTimeReturned;
3) Finally to view the results on your form create a text box on your form with this as the control source:
=ElapsedTimeString([DateTimereturned],[DateTimecalled])
**Let me know if you have any questions....I actually got all of this from a access book that I Have that I can reference for any follow up questions that could arise.