How to get next row value in query rows (Access 2002)

[September18]

I have the following table in Access 2002:
Empl | Date | Location
0001 | 12/1/1990 | POA
0001 | 3/1/1992 | SYL
0001 | 6/1/2000 | VNY
0002 | 4/3/2002 | TOA
0002 | 8/15/2006 | PAA

I need to create another column named EndDate that will pick up the next row value like this:
Empl | Date | Location | End Date
0001 | 12/1/1990 | POA | 3/1/1992
0001 | 3/1/1992 | SYL | 6/1/2000
0001 | 6/1/2000 | VNY | null
0002 | 4/3/2002 | TOA | 8/15/2006
0002 | 8/15/2006 | PAA | null

Could someone help please?

[nico5038]

In general I use an autonumber to get access to a previous row.
When you add an autonumber field to the table you can create a query with:
select autonumfield - 1 as OldKey, ...

Joining the original table with the query by the autonum field will give the possibility to show two rows at the same time. Now an IIF statement can create the Enddate like:
EndDate:IIF(Key = OldKey,[Date],Null)

Best to rename the Date field to e.g. StartDate, as Date is also a function and thus a reserved word and can cause trouble!

Nic;o)

[ADezii]

You cab do it in code by created Nested Loops both pointing to the same Recordset, but with only the inner loop updating.

[September18]

Thanks for all the suggestions.

[nico5038]

Keep us posted :-)

Nic;o)

[September18]

Thank you sooooooooo much nico5038 . I GOT it. I did what you suggested. You're super. Thanks again.

[nico5038]

Glad I could help.

Be sure to have no gaps in the autonumber sequence, else the trick won't work.

Nic;o)