Report next value in sequence to current record

[skyguy]

[[Split from: Row-Numbering a Query based on Date Values ]]

Thank you so much Luuk! Very helpful!

Could you help me with one more thing? Now that the rows have sequential IDs, how could I create a field that lists the timestamp in the very next row in each row? For example, I would like to have the following:

Lot    Status     Timestamp              Next_Timestamp
342    Down       4/25/2015 6:49:00 AM   4/25/2015 8:30:00 AM
526    Up         4/25/2015 8:30:00 AM   4/25/2015 9:21:43 AM
782    Up         4/25/2015 9:21:43 AM   4/25/2015 9:55:22 AM  
827    Down       4/25/2015 9:55:22 AM

I understand how to accomplish this in my head, but haven't been using Access long enough to know the syntax required. I think that using the DLookup function with a criteria that selects the Timestamp value from the row who's ID value is the minimum greater-than value of the current row's ID value, will work. Could you help with the syntax?

Thank you so much (again)

[Luuk]

"I understand how to accomplish this in my head, but haven't been using Access long enough to know the syntax required. I think that using the DLookup function "

actually: I understand how to accomplish this in my head, but haven't used Access often enough to know the syntax ;-)

You want to find a value which is bigger (next) to the one you found.

In other words, you want to find the SMALLEST value that is bigger tnan the one you already have

use DMIN() see:

https://msdn.microsoft.com/EN-US/library/office/ff834804.aspx

It has the same syntax as DCOUNT

[jforbes]

Are you wanting to display this information on a Form or Report/Query?
If you are on a Form, you could use the method Luuk is describing in the OnCurrent Event of the Form.

If you would like your results in a Query, you could join back to your original table, something like this:

SELECT Lots.ID, Lots.Lot, Lots.Status, Lots.TimeStamp, NextLot.TimeStamp AS NextTimeStamp
FROM Lots
LEFT JOIN Lots AS NextLot
ON Lots.ID+1=NextLot.ID

This will only work if the ID's are sequential. If your ID's skip around, the query gets more complex.

[skyguy]

Hi jforbes, I was trying to implement this in a query. The following code worked perfectly, and doesn't require sequential IDs:

Time_Next: DMin("Timestamp","W_Table","[ID] >" & [ID])

Thank you very much for your input!