How DMax the record prior to the max record

I'm using this as the control source for a field on a form.  It work fine to give me the last (max) date on the field.

But in another field on the form I need to provide the record just prior to the Max record.

Here is my code for the Max record:

=DMax("[Discharge Date]","ER Visit","[EmpID] = " & [Forms]![frmEmpMaster]![txtEmpID])

How do I code to get the record before the max record?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
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...

Jeffrey CoachmanMIS LiasonCommented:

I apologize for not being specific enough...

In your case you could do something like this in our query:
SELECT YourTable.ID, YourTable.EmpID, YourTable.YourValue
FROM YourTable
WHERE (((YourTable.EmpID)=[Forms]![Form1]![txtEmpID]))
ORDER BY YourTable.EmpID DESC , YourTable.YourValue DESC; get the query to filter for the EmpID on your form.

Then do something like this on the form control:
=DMax("YourValue","YourQuery","YourValue<" & DMax("YourValue","YourTable","EmpID=" & [txtEmpID]))

New sample attached.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
...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?
Eric ShermanAccountant/DeveloperCommented:
I generally do that with a recordset because of what Pat Hartman posted ...

Dim rst As Recordset
Dim dteDischargeDate As Date
Set rst = CurrentDB.OpenRecordset("SELECT * FROM [ER Visit] WHERE [EMPID] = " & [Forms]![frmEmpMaster]![txtEmpID] & " ORDER BY [Discharge Date] Desc;", dbOpenDynaset)
If rst.RecordCount = 0 Then
    Exit Sub
End If
If rst.RecordCount = 1 Then
    dteDischargeDate = rst![Discharge Date]
End If
If rst.RecordCount >1 Then
    dteDischargeDate = rst![Discharge Date]
End If

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.