Use a function like this:
Public Function FormatYearDayHourMinuteSecondDiff( _
ByVal datTimeStart As Date, _
ByVal datTimeEnd As Date, _
Optional ByVal strSeparatorDate As String = " ", _
Optional ByVal strSeparatorTime As String = ":") _
As String
' Returns count of years, days, hours, minutes and seconds of difference
' between datTimeStart and datTimeEnd converted to
' years, days, hours and minutes and seconds as a formatted string
' with an optional choice of date and/or time separator.
'
' Should return correct output for a negative time span but
' this is not fully tested.
'
' Example:
' datTimeStart: #2006-05-24 10:03:02#
' datTimeEnd : #2009-04-17 20:01:18#
' returns : 2 328 09:58:16
'
' 2007-11-06. Cactus Data ApS, CPH.
Const cintSecondsHour As Integer = 60& * 60&
Dim intYears As Integer
Dim intDays As Integer
Dim intSeconds As Integer
Dim intHours As Integer
Dim datTime As Date
Dim strDatePart As String
Dim strTimePart As String
Dim strYDHMS As String
intYears = Years(datTimeStart, datTimeEnd)
datTimeStart = DateAdd("yyyy", intYears, datTimeStart)
intDays = DateDiff("h", datTimeStart, datTimeEnd) \ 24
datTimeStart = DateAdd("d", intDays, datTimeStart)
intHours = DateDiff("h", datTimeStart, datTimeEnd)
datTimeStart = DateAdd("h", intHours, datTimeStart)
intSeconds = DateDiff("s", datTimeStart, datTimeEnd)
' Format year and day part.
strDatePart = CStr(intYears) & strSeparatorDate & CStr(intDays)
datTime = TimeSerial(intHours, 0, intSeconds Mod cintSecondsHour)
' Format hour, minute and second part.
strTimePart = Format(datTime, "hh\" & strSeparatorTime & "nn\" & strSeparatorTime & "ss")
strYDHMS = strDatePart & " " & IIf(datTime < 0, "-", "") & strTimePart
FormatYearDayHourMinuteSecondDiff = strYDHMS
End Function
Just modify strDatePart and strTimePart to fit your need.
Then in your query:
NewOpen_Time: FormatYearDayHourMinuteSecondDiff([New],[Opened])
Edit
The calculation of years you can just remove, as I guess it will not be relevant here, or you can use this function:
' Returns the difference in full years between Date1 and Date2.
'
' Calculates correctly for:
' negative differences
' leap years
' dates of 29. February
' date/time values with embedded time values
' any date/time value of data type Date
'
' Optionally returns negative counts rounded down to provide a
' linear sequence of year counts.
' For a given Date1, if Date2 is decreased stepwise one year from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If LinearSequence is False, the sequence will be:
' 3, 2, 1, 0, 0, -1, -2
' If LinearSequence is True, the sequence will be:
' 3, 2, 1, 0, -1, -2, -3
'
' If LinearSequence is False, reversing Date1 and Date2 will return
' results of same absolute Value, only the sign will change.
' This behaviour mimics that of Fix().
' If LinearSequence is True, reversing Date1 and Date2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28th when adding a count of years to dates of Feb. 29th
' when the resulting year is a common year.
'
' 2015-11-24. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function Years( _
ByVal Date1 As Date, _
ByVal Date2 As Date, _
Optional ByVal LinearSequence As Boolean) _
As Long
Dim YearCount As Long
Dim DayCount As Long
DayCount = DateDiff("d", Date1, Date2)
If DayCount = 0 Then
' The dates are equal.
Else
' Find difference in calendar years.
YearCount = DateDiff("yyyy", Date1, Date2)
' For positive resp. negative intervals, check if the second date
' falls before, on, or after the crossing date for a 1 year period
' while at the same time correcting for February 29. of leap years.
If DayCount > 0 Then
If DateDiff("d", DateAdd("yyyy", YearCount, Date1), Date2) < 0 Then
YearCount = YearCount - 1
End If
Else
If DateDiff("d", DateAdd("yyyy", -YearCount, Date2), Date1) < 0 Then
YearCount = YearCount + 1
End If
' Offset negative count of years to continuous sequence if requested.
If LinearSequence = True Then
YearCount = YearCount - 1
End If
End If
End If
' Return count of years as count of full year periods.
Years = YearCount
End Function
