Subtracting End Time from Next Start Time (1 Viewer)

[vsssr]

I'm in the weird situation of having to calculate the Time between the End Date of 1 record and the Start Time of the Next Record.
Like so:

Name Action Start End
Bill Sent 03/4/02 14:50 03/4/02 15:05
Bill Closed 03/4/02 15:25 03/4/02 14:03

In this case:
(03/4/02 15:05) - (03/4/02/ 15:25)

I've been stumped on this for a while so any help would be much appreciated

thanks,
Justin

 

[RV]

Try this:

SELECT YourTable.StartTime, YourTable1.EndTime, Format(YourTable.StartTime-YourTable.EndTime,"hh:nn")
FROM YourTableTimeTable AS YourTable1
WHERE YourTable.StartTime>=
YourTable1.EndTime
AND YourTable1.EndTime=
(SELECT MAX(YourTable2.EndTime)
FROM YourTable AS YourTable2
WHERE YourTable2.StartTime<YourTable.StartTime);

being yourTable the name of thetable you're using.

For more information / help search this forum for "Total time".
There is also a lot of information about how to format dates and times in the Microsoft KB,
for example (Access97):
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q88657

Suc6,

RV

 

[vsssr]

thanks very much!

one question though...it looks like the formula you provided is assuming on my Start Dates and End Dates being from different tables. Can I use this if they are from the same table, just different records?

thanks again for your help!
Justin

 

[RV]

Justin,

>it looks like the formula you provided is assuming on my Start Dates and End Dates being from different tables<

I've used a so called autojoin.
In situations like this one, you'll have to compare records within the same table.
In a autojoin you'll have to use aliases.
You're referring in your query to the same physical table though.

So,
>Can I use this if they are from the same table, just different records?<
actually, they ARE from the same table.

RV