How DMax the record prior to the max record

Posted on 2014-08-11
Last Modified: 2014-08-25
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?

Question by:SteveL13
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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...

    LVL 74

    Accepted Solution


    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.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    ...or this:
    =DMax("YourValue","YourQuery","YourValue<" & [txtMax])
    LVL 33

    Expert Comment

    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?
    LVL 19

    Assisted Solution

    by:Eric Sherman
    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now