Calculate Elasped EndTime To Next Record StartTime field

[JoeAtBell]

This is my first post and would like some help on making a table from an existing table that has PN, StartTime, EndTime fields. I am trying to find the calculated time from one record's end time to the next records StartTime. Any examples or help will be appreciated.
Joe

[RhinoBull]

How do you store Start/End time?

[Static]

First post!?? its says 2! lol j/k


what are you doing this in? Access?

[JoeAtBell]

I am working with MS-Access 2003 and the the data is in a table like :
PN is Text, StartDAte is DateTime, EndDAte is DateTime.

[Static]

well it depends on what u want to have as elapsed time...
Hours? days? mins? all??

here is a simple sample...

SELECT Table5.PN, Table5.StartDate, Table5.EndDate, [EndDate]-[StartDate] AS Elapsed INTO ElapsedTime
FROM Table5;

[JoeAtBell]

Thanks for the sample, but it looks like this would look at the current row only. My real problem is that the Table is sorted by StartDate and I am trying to subtact the StartTime form the PREVIOUS records EndTime. Or the EndTime from the current record the the StartTime of the NEXT record.

Joe

[JoeAtBell]

Start and End times are in a MS-Access 2003 table. both are formated datetime

[JoeAtBell]

Yes I'm using MS-Access 2003 but I'm trying to use a Module in VBA for this action.

[Al42]

Static's code would create a table named ElapsedTime with all records with the elapsed time from start to end for that record.

If you wanted end of that record to start of the next record interval, create a recordset of * and walk it from the first record to the last record, saving the current record's end time in a variable, move to the next record, subtract the variable from the current record's start time (this is your "inter-record" time interval), record the current record's end time in the variable ... and repeat till the last record. What you do with the time interval at each step is up to you.

[JoeAtBell]

thanks for all your help. I bought a couple of new books to read up on VBA and try to incorporate your suggestions.