formula for subtracting time in next row 2

[ziggs]

I'm looking for a formula for a query that will subtract the time in seconds when comparing one row to the next row. For example, here's the time

1 08/10/08 09:08:07
2 08/10/08 09:09:00
3 08/10/08 09:09:37
4 08/10/08 09:10:45

and I would like to see the amount of seconds like this:

1 08/10/08 09:08:07 null
2 08/10/08 09:09:00 53 seconds
3 08/10/08 09:09:37 37 seconds
4 08/10/08 09:10:45 68 seconds

I tried something like
diff: (DateDiff("s",[DateTime1],[DateTime1])) but that didn't work. DateTime1 is the name of the DateTime column.

 

[JBinQLD]

G'day

The key is of course retreiving the previous value. Assuming that column on the left is an ID field I'd try something like:

Function CompareTimes(intID As Integer) As Date
Dim dteCurrentTime As Date
Dim dtePreviousTime As Date
Dim intPreviousID As Integer


dteCurrentTime = DLookup("DateTime1", "YourTable", "ID=" & intID)
intPreviousID = Nz(DMax("ID", "Yourtable", "ID<" & intID), 0)
If intPreviousID = 0 Then
    'first value, there is no previous time
    'put in a dummy value
    CompareTimes = #1/1/2000#
Else
    dtePreviousTime = DLookup("DateTime1", "YourTable", "ID=" & intPreviousID)
End If

CompareTimes = DateDiff("s", dteCurrentTime, dtePreviousTime)

End Function

Far from neat and tidy but should give you an idea. And being a function you can use it in a control or query...

JB

 

[ziggs]

Sorry, I'm new to Access. I created the Function in the Module area, hope that's right. Now, how do I call the function in the "Select Query" section?

 

[JBinQLD]

Create a new query, choose your table. Add the ID column and the DateTime1 Column. In the third column type CompareTimes(ID) where ID is the name of your ID column. Access will change this to Expr1: CompareTimes([ID])

Run the query and it should give the output you requested.

Alternate:

Create a form based on your table. Make this a continuous form (Default view property). Add the two fields as text box controls. Add a third text box control and for it's control source type =CompareTimes(me!ID).

Open the form and again you should see your result.

JB

 

[MajP]

You can do this in Sql as well. I am no SQL expert so you may want to post in the Query forum to get a more eloquent solution. I break it down Barnie style when I do it.

1) Return each record and the previous value. My table is called "tblRptTime", and my field is "dtmRptTime"

qryPreviousTime
SELECT A.dtmRptDate, 
Max(B.dtmRptDate) AS PreviousTime
FROM tblRptDate AS A, 
tblRptDate AS B
WHERE ((([A.dtmRptDate])>[b.dtmRptDate]))
GROUP BY A.dtmRptDate
ORDER BY A.dtmRptDate;

If I want to show the very first record I then use a left outer join and the date diff function

SELECT tblRptDate.dtmRptDate, 
DateDiff("S",[PreviousTime],[tblRptDate].[dtmRptDate]) AS Diff
FROM tblRptDate 
LEFT JOIN qryPreviousTime 
ON tblRptDate.dtmRptDate = qryPreviousTime.dtmRptDate

Example output
dtmRptDate diff
1/1/2008 2:27:00 PM
1/1/2008 2:27:30 PM 30
1/1/2008 2:27:50 PM 20
1/1/2008 2:28:00 PM 10
1/1/2008 2:29:25 PM 85
1/1/2008 6:27:00 PM 14255
1/1/2008 7:28:00 PM 3660

 

[ziggs]

Thanks for helping. Unfortunately, I got an error on this line. If acceptable, I can just email you the Access 2000 document that I'm using to make this faster.

I got an error message on this line:

dteCurrentTime = DLookup("DateTime1", "Log", "ID=" & intID)

invalid use of null.

I'm totally confused.

Sorry.

 

[JBinQLD]

I would gladly help by email but the sharing of email addresses in this forum is not promoted. There ois an attachment option at the bottom of this post window but i've never tried it before.

When the code debugs, hover your mouse over intID and tell me if there is a value. This line is trying to lookup the value of the DateTime1 for the record with that ID. Look in your table and ensure that the record does have a valid time.

 

[JBinQLD]

Failing that of course, there is the option of using MajP's SQL option! It's using the same idea but applied differently. If you stick with the VBA method then once you have it working let me know cos I've spotted a tweak to improve the result of the first record....

 

[ziggs]

Sorry MajP, i'm over my head here. The Date and Time field that I'm using has to be converted in the query itself to make it look like a Date/Time field, here's code "Expr1: Mid([field2],4,20)". Does that make since?

 

[JBinQLD]

Mate, can you describe your table using the matrix below please:

TableName: xyz
FieldName DateType



And then paste just two rows of actual data here?

 

[ziggs]

TableName: Log

ID=AutoNumber
Status=text
DateTime1=text

In Select Query, I use

Expr1: Mid([DateTime1],4,20)

to convert the time properly

 

[JBinQLD]

This will NOT convert the string to a date. Even though it may look like a date to a human, to a computer it's still a sequence of characters. You can

a) Make the field date/time, set to General Date as format [red](recommended)[/red]

- OR -

b) Adjust just statement thus:

Expr1: CDate(Mid([DateTime1],4,20))

The CDate function converts a valid input into what Access can actually resolve as a date. it stands for C(onvert to)Date

Similarly there are cstr, cint, clng, etc functions.

Get this bit sorted then we'll re-group!

JB

 

[ziggs]

Within the table, it did not allow me to convert to General Date. However, within the Query, I was able to successfully use your "Expr1: CDate(Mid([DateTime1],4,20))"

I then applied the function that you supplied earlier:

Function CompareTimes(intID As Integer) As Date
Dim dteCurrentTime As Date
Dim dtePreviousTime As Date
Dim intPreviousID As Integer


dteCurrentTime = DLookup("DateTime1", "YourTable", "ID=" & intID)
intPreviousID = Nz(DMax("ID", "Yourtable", "ID<" & intID), 0)
If intPreviousID = 0 Then
'first value, there is no previous time
'put in a dummy value
CompareTimes = #1/1/2000#
Else
dtePreviousTime = DLookup("DateTime1", "YourTable", "ID=" & intPreviousID)
End If

CompareTimes = DateDiff("s", dteCurrentTime, dtePreviousTime)

End Function

But received an error message of:

runtime error 94:
invalid use of null

 

[Remou]

Paste this into Query Design in SQL view:

SELECT Log.ID, Log.Status, Log.DateTime1, 
   (SELECT TOP 1 t.DateTime1 
    FROM Log t 
    WHERE t.DateTime1>log.DateTime1) AS NextRec, 
DateDiff("s",[DateTime1],[NextRec]) AS Diff
FROM Log
ORDER BY Log.DateTime1

Does it suit?

 

[ziggs]

Sorry Remou, it didn't work and came up with an "error" message.

 

[Remou]

What error?

 

[JBinQLD]

G'day,

As you're unable to convert the field in the table you will need to make a change to the code - it was provided assuming the DateTime1 field was of type date/time. Try this:

Function CompareTimes(intID As Integer) As Date
Dim dteCurrentTime As Date
Dim dtePreviousTime As Date
Dim intPreviousID As Integer


dteCurrentTime = cdate(Mid(DLookup("DateTime1", "YourTable", "ID=" & intID),4,20))
intPreviousID = Nz(DMax("ID", "Yourtable", "ID<" & intID), 0)

If intPreviousID = 0 Then
    'first value, there is no previous time
    'put in a dummy value
    dtePreviousTime=dtePreviousTime = cdate(mid(DLookup("DateTime1", "YourTable", "ID=" & intID),4,20))
Else
    dtePreviousTime = cdate(mid(DLookup("DateTime1", "YourTable", "ID=" & intPreviousID),4,20))
End If

CompareTimes = DateDiff("s", dteCurrentTime, dtePreviousTime)

End Function

But Ireally think you should look at why you can't convert the table field to date time as it would save these conversions. How about creating a new column and copying the contents across? i'm sure it will make for less problems later on..

JB

 

[JBinQLD]

PS. Ziggs, I must acknowledge your perserverance and persistance with this problem. You've been at it since I got to work at 7.30 this morning, that's almost twelve hours now. I don't know what time zone you're in but well done on keeping trying!

I'm sure someone will read this whole thread one day and get some inspiration from your determination!

Good on ya,

JB

 

[Remou]

JBinQLD
Why use a UDF when this comparison can be done with pure SQL?

 

[JBinQLD]

Remou, I really don't mind of course which option the OP chooses, it was just the route I first struck upon when i read the q this morning so I stuck with it. About half way through the day I suggested he try MajP's SQL solution but when that didn't work I just dug around some more to try and get the guy a solution.

I guess I've become blinkered into getting it working now and hopefully giving a little coincidental training on the way.

finally, my lack of expertise. I can normally write pretty accurate code without access to Access but I prefer to test SQL before posting it on here. Most of the day I only visit this site on a mobile device during smoko so no MS Access or SQL Server unless I fire up a citrix session!

JB