DateDiff to calculate time elapsed between current and other records.
Posted on 2003-03-11
Am using an adodb recordset to pass values from a set of masked edit boxes into a sql table. The purpose of this exercise is to log tasks and their duration in a time recording program. One of the edit boxes therefore passes to a 'duration' field. However if a user does not enter a duration, I would like the program to automatically calculate what the duration is.
I assume i use the DateDiff function, but don't know how to go about picking up values from records other than the current one.
I have tried declaring a 'duration' string in the DateDiff expression and picking up values for it by moving between records, but get 'invalid procedure call or argument' error.
heres the code:
Private Sub cmdUpdate_Click()
'The code for date and time can be input as date and time and then joined
'in a string to bring both items together, that string is then added to the
'recordset as the whole date and time.
Dim ShortDate As String
Dim ShortTime As String
Dim DateTime As String
Dim DurCalc As String
Dim NextDur As String
'in my database table i have fields start_date_time, cost_centre, task_code, duration
DateTime = Format(ShortDate, "dd/mm/yyyy") & " " & Format(ShortTime, "hh:mm:ss")
rec.Fields(1) = DateTime
rec.Fields(2) = Format(mebCCentre.Text, ">")
rec.Fields(3) = Format(mebTask.Text, ">")
If rec.EOF = True Then
rec.Fields(4) = Format(mebDur.Text, "0")
NextDur = rec.Fields(1)
rec.Fields(4) = DateDiff("ssss", rec.Fields(1), NextDur)