Create calculated query for time difference

N

navin

Hi,

In a list box i am displaying the request number and request date and
time for a user after reteriving from table. i need to add one extra
column which should display the ageing of the request by taking the
difference between request date and time and current date/time.

please tell me how this can be done.

thanks,
navin
 
W

Wayne-I-M

Hi Navin

You could use a calculated column in the query the combo is based on

Difference:DateDiff("d", TableName!FieldName,Date())
 
K

Ken Sheridan

The following function allows you to return the difference between two
date/time values, either as, for instance, 2 days 3:12:58, or 51:12:58

Public Function TimeDuration( _
dtmFrom As Date, _
dtmTo As Date, _
Optional blnShowDays As Boolean = False) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double

dblDuration = dtmTo - dtmFrom

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

If blnShowDays Then
'get days and hours
strDaysHours = lngHours \ HOURSINDAY & _
" day" & IIf(lngHours \ HOURSINDAY <> 1, "s ", " ") & _
lngHours Mod HOURSINDAY

TimeDuration = strDaysHours & strMinutesSeconds
Else
TimeDuration = lngHours & strMinutesSeconds
End If

End Function

Paste the function into a standard module and in the list box's underlying
RowSource query call it with, to show the days:

TimeDuration([Request Date], Now(), True)

or to show it as total hours:

TimeDuration([Request Date], Now())

BTW, as someone pointed out recently, it does not cater for daylight saving
time either starting or ending within the range in question.

Ken Sheridan
Stafford, England
 
Top