filling in blank fields with next records values

[Headache..! via AccessMonster.com]

hihi,

i have a table that looks like this

LotNo Date TimeStart TimeEnd
11111 01-Jan-08 13:30 13:35
11112 02-Jan-08 08:00
11113 02-Jan-08 08:10
11114 02-Jan-08 08:20 08:30

it is understood in my workplace that those blank [TimeEnd] is equal to the
next [TimeStart],

however when I use this table's information to calculate the duration of time
in a query, I am stuck as there is no value in the [TimeEnd].

Is is possible to get auto fill in the next record's Start time when the
field is blank in a query..?


pls help..!
stuck for 2days

 

[Allen Browne]

Use a subquery to get the next non-blank TimeEnd.

Type an expression like this into the Field row in query design:
(SELECT TOP 1 TimeEnd FROM Table1 AS Dupe
WHERE Dupe.LotNo > Table.1LotNo
AND Dupe.TimeEnd Is Not Null
ORDER BY Dupe.LotNo)

Once you have that working, the expression will be like this:
RealEnd: IIf([TimeEnd] Is Null,
(SELECT TOP 1 TimeEnd FROM Table1 AS Dupe
WHERE Dupe.LotNo > Table.1LotNo
AND Dupe.TimeEnd Is Not Null
ORDER BY Dupe.LotNo), [TimeEnd])

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Note that the subquery resutls will be read-only. If you need editable
results, turn the subquery into an Update query, use criteria to limit to
records where TimeEnd is Null, and execute the update so the TimeEnd field
gets updated.