How to get next row value in query rows (follow-up question)

I've searched for the thread you mention but was unable to find it.

Perhaps you could post a link - then we could start to look into this for you.

I believe this is the old post:
Link

Also, I managed to get what I believe is the solution.
I noticed you're using RecNum as join field and trying to calculate RecNum-1. It looks to me RecNum is textual field and therefore can not be used in calculations.
What I did is this:
1. I created a table containing all the fields you listed and added a new field named ID (autonumber)
2. I created a Query1 based on that Table1 including all the fields and adding a new field: OldID:[ID]-1
3. I created second query based on table and query1.
These two are left joined Table1.ID->Query1.OldID
this query2 contains all the fields from Table and PriceDate field from query2

Here is how it looks:

[HTML]ID Table1.PriceDate Price Dividend RecNum Query1.PriceDate
1 01.08.2008 10,00 1,00 {916E47CB-015A-466D-A2D0-...} 02.08.2008
2 02.08.2008 11,00 2,00 {CDE5006B-5D05-4A35-B144-...} 03.08.2008
3 03.08.2008 12,00 2,00 {AF81D70E-B18F-405F-9D87-...}[/HTML]
hope this is what you wanted to have.

I believe this is the old post:
Link
...

Good for you H.

I'll wait to see how the OP fares with this :)

Thanks guys! This did the trick. Although I was using "autonum" for the field, I had it set to field format of "replication ID" rather than "integer long". I thinks some MS help page suggested this and I foolishly followed that lead!

Foolish FinanceGuy!

That's like copying their example code. It never follows their own naming conventions even :D It is rarely any good for anything other than illustrating the point and giving an example in context. It does usually help - but is always worth taking with a pinch of salt.

PS. With my fairer hat on, this isn't entirely true in all cases. Some follow that trend, but I have found some reasonable code on MSDN and other MS provided resources.

Hi all. I'd be a little cautious about using [ID]-1 as a join to return the previous row along with the next, unless you are certain that the autonumbers are entirely contiguous throughout. If the autonumber has skipped one or more values the [ID]-1 join will fail to return certain rows. This is mentioned by Nico in his final post in the linked thread.

As you know, autonumbers are never re-used, so if a user simply cancels entry of the current record (or makes an entry then subsequently deletes the record) the gap between the autonumber for the previous record and the next in sequence will be at least 2, not 1.

I use the max of the joined ID where less than the current ID for such joins, as it works regardless of the gap in value between consecutive autonumbers.

Another point to consider is that it is implicit in this case that the rows will always be entered in ascending date order. This may well always be true for this application, but in other circumstances larger autonumber IDs might not correspond to later date entries - a user may enter data in descending date order, or not in order at all, in which case matching the previous ID does not correspond to matching the previous date.

-Stewart

Following on from Stewart's post, linking on the Max(subQ.PriceDate), WHERE subQ.PriceDate < Auto_Price_Tbl.PriceDate might be the safest and most appropriate way to link to the previous record in this case.