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.

 

[aperez54]

WOWZA
you guys are awesome for continuing to help! i'm a lurker but for sure i'm going to need this sometime soon!

 

[ziggs]

Remou,

An error message of #Error shows in the field created called Diff. That's the error.

 

[ziggs]

JBinQLD, Thanks for trying, but I still get an error message of runtime error 13, type mismatch. Debug highlights the line of:

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

 

[Remou]

Your table is called Log, and your field is called datetime1 and it is text, is this correct? If you type into the immediate window:

?DateDiff("s",Now(),Now())

Do you get zero or an error?

(I tested this in Access 2000 with the data you show above.)

 

[ziggs]

i get 0

 

[Remou]

So:

?DateDiff("s",Dlookup("DateTime1","Log"),Now())

 

[ziggs]

runtime error 13, type mismatch

 

[Remou]

?DateDiff("s","08/10/2008 09:08:07",Now())

 

[ziggs]

2011219

 

[Remou]

Whee.

So what is in you table in the first DateTime1 field? And what type of field is it? Because what we have just tested is a text field containing the data that you show in your first post.

 

[ziggs]

Tue 09/02/2008 10:41:31.35

Is what the batch file produces for a time and then I import it into Access.

When I originally wrote the first post, I never mentioned, as was figured out in mid posting, that the field that I originally posted was from a query, not a table, and I used Expr1: Mid([DateTime1],4,20)) to get the results that I thought would work, but didn't. Sorry for the confusion!

 

[Remou]

Ok. Try this:

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

 

[ziggs]

Still an error. I used same 3 intermediate windows with same results, except for:

?DateDiff("s","08/10/2008 09:08:07",Now())

The result was: 2012980

 

[Remou]

Did you paste the above SQL into an SQL view, Query design?

 

[ziggs]

yes

 

[Remou]

Please paste this into a query and paste back the results. You can reduce it to the first few records.

SELECT Log.ID, log.DateTime1
FROM Log
ORDER BY Log.DateTime1

 

[ziggs]

ID DateTime1
1 Tue 09/02/2008 10:41:31.35
2 Tue 09/02/2008 10:41:32.84
3 Tue 09/02/2008 10:41:34.26
4 Tue 09/02/2008 10:41:35.75

 

[JBinQLD]

Who, what is the .35, .84, etc business going on?

Ziggs, when you say you got a type mismatch, type into the immediate window:
?dteCurrentTime

and then
?dtePreviousTime

and paste back the results can you pls?

JB

 

[Remou]

Here:

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

 

[ziggs]

Thank you so much for sticking with me!!! That last one did it.