DateDiff

[Downie]

I am having a prblem with DateDiff. The below method works when the
dates are in the same day. However when the start and end times fall
on different days I get a (-)negative total of minutes.

ProcessTime = (ProcessTime + (DateDiff("n", StartTime,
rst3!msglog_crtdt)))

For example:
Start - 11:30pm 7/1/2006
End - 1:20am 7/2/2006

The total minutes is showing up negative?

Is there any way that I can fix this?

Thanks
Rich

 

[Douglas J. Steele]

Does StartTime contain the date and time, or only the time? Date/time values
in Access are supposed to be complete timestamps (i.e. Date and Time) in
order to work properly.

If you cannot (or will not) include the date, see whether
http://www.mvps.org/access/datetime/date0008.htm at "The Access Web" helps.

 

[Downie]

Yes, I am using Date and Time

(7/1/2006 2:30:39 AM)

 

[Downie]

Is this on the right track?

ProcessTime = (ProcessTime + (DateDiff("d", StartTime,
rst3!msglog_crtdt)) >= 1, (DateDiff("d", StartTime, rst3!msglog_crtdt))
/ 1440))), (DateDiff("n", StartTime, rst3!msglog_crtdt)

 

[Douglas J. Steele]

And what does rst3!msglog_crtdt contain?

If it's a date/time as well, DateDiff is all you should require, without any
other manipulations:

?DateDiff("n", #7/1/2006 23:30#, #7/2/2006 1:20#)
110

 

[Downie]

Thanks for your help here Douglas
Yes...all dates I'm using are in Date/Time format.
Here is the entire cron I am trying to work

Some of my Failure Total minutes are coming back negative(-) in the
jobs table

SQLString = "SELECT jobs.qrt, jobs.year, jobs.Branch_Number,
jobs.active_jobs_msglog_crtdt, jobs.completed_jobs_msglog_crtdt,
jobs.item_id, jobs.actual_process_time, jobs.Number_Of_Failures,
jobs.Failure_Time "
SQLString = SQLString & "FROM jobs "
SQLString = SQLString & "WHERE (((jobs.qrt)= " & rst!qrt & ")
AND ((jobs.year)= " & rst!year & "));"
cmd2.CommandText = SQLString
rst2.CursorLocation = adUseClient
rst2.Open cmd2, , adOpenKeyset, adLockOptimistic

If rst2.RecordCount > 0 Then
Do While Not rst2.EOF
SQLString = "SELECT downloads.Branch_Number,
downloads.item_id, downloads.msglog_text, downloads.msglog_crtdt "
SQLString = SQLString & "FROM downloads "
SQLString = SQLString & "WHERE ((downloads.Branch_Number) =
" & rst2!Branch_Number & ") AND ((downloads.item_id) = " & rst2!item_id
& ") ORDER BY downloads.msglog_crtdt;"

cmd3.CommandText = SQLString
rst3.CursorLocation = adUseClient
rst3.Open cmd3, , adOpenKeyset, adLockOptimistic

' MsgBox (rst3.RecordCount)
' MsgBox (rst3.GetString)

If rst3.RecordCount > 0 Then
FA = "N"
Failures = 0
ProcessTime = 0
StartTime = 0

Do While Not rst3.EOF
If FA = "Y" Then
If InStr(rst3!msglog_text, "Abnormal") Then
Failures = (Failures + 1)
ProcessTime = (ProcessTime + (DateDiff("n",
StartTime, rst3!msglog_crtdt)))
FA = "N"
ElseIf InStr(rst3!msglog_text, "Normal") Then
ProcessTime = (ProcessTime + (DateDiff("n",
StartTime, rst3!msglog_crtdt)))
FA = "N"
Else
'Do Nothing
End If
Else
If (InStr(rst3!msglog_text, "Active")) Then
FA = "Y"
StartTime = (rst3!msglog_crtdt)
End If
End If
rst3.MoveNext
Loop
rst2!actual_process_time = ProcessTime
rst2!Number_Of_Failures = Failures
rst2!Failure_Time = (DateDiff("n",
rst2!active_jobs_msglog_crtdt, rst2!completed_jobs_msglog_crtdt)) -
ProcessTime
rst2.Update
End If
rst2.MoveNext
rst3.Close
Loop
rst2.Close
End If
DoCmd.SetWarnings True
End Sub

 

[Douglas J. Steele]

Is this where you're complaining about getting negative values?

rst2!Failure_Time = (DateDiff("n",rst2!active_jobs_msglog_crtdt,
rst2!completed_jobs_msglog_crtdt)) - ProcessTime

Inside your loop for rst3, you initialize StartTime to 0. Unless StartTime
gets set to a realistic date/time, your values for ProcessTime are going to
be HUGE (a date/time value of 0 is midnight on 30 Dec, 1899)