How DMax the record prior to the max record

I have seen this done a couple of times, ...

Here is how I did it once...

Make a query like this:
SELECT YourTable.ID, YourTable.YourValue
FROM YourTable
ORDER BY YourTable.YourValue DESC;
(Presuming that you have some sort of "ID" primary Key)
This will sort al the values in the table form "Max to Min"

Then do this in control source of a textbox on your form:
=DMax("YourValue","YourQuery","ID<" & DLookUp("ID","YourTable","YourValue=" & DMax("YourValue","YourTable")))

Here is a sample
Database26.mdb

I am sure another expert can show you a query to get the "Next lower" value, ...then look that up...
But again, what I posted above is how I did it once.
I had the file available so I just posted it...
;-)

JeffCoachman

...or this:
=DMax("YourValue","YourQuery","YourValue<" & [txtMax])

You should probably rethink this requirement.  In a relational database, no row in a table has any relationship to any other row.  All data is retrieved via queries and both tables and queries are unordered sets.  That means that when a query runs, you can't predict the order in which rows will be returned UNLESS your query includes an order by on a unique column.  In practice, the results are not random so people assume something that the relational model does not provide.  Assuming, no intervening data changes, running the same query multiple times would result in the same records being returned in the same order.  However, if data were changed, that could alter the sequence of the records returned and since we rarely examine every row in detail and compare it to previously returned sets, we are not likely to ever notice this unless it affects one of the first few rows that we are accustomed to seeing.

Now, the question is - what relationship are you assuming that the "previous" record has to the "current"?  Perhaps your problem is caused by improper normalization.   Are you storing data in each record that should more correctly be stored in a parent record?