[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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?

  • 4
2 Solutions
Jeffrey CoachmanCommented:
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 CoachmanCommented:
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 CoachmanCommented:

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;
...to 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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Jeffrey CoachmanCommented:
...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


Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now