[SOLVED] Microsoft Access DLookup based on Entry Date

Microsoft Access DLookup based on Entry Date

I am a newbie at Microsoft Access and have been trying to wrap my head around an issue.
I am using a form to search for an application number, based on a table (UpdatedFiles).

I am trying to get the form to display the latest entered status on a given application number. However, when I attempt to do so, the result always pulls up the earliest entered status on a given application number.

My form has code which updates the entry date (ENTRYDT) once the record is saved.

I am basically attempting to use the latest ENTRYDT when I search up the application number (APPID). APPID is the table's primary key.

Here's what my code looks like:

Private Sub SearchCommand_Click()
Dim strfilapp As String
Dim strcheck As Variant


strfilapp = "[APPID] = " & "'" & Me!APPID & "'"

strcheck = DLookup("[APPID]", "UpdatedFiles", strfilapp)

If Not IsNull(strcheck) Then
    On Error Resume Next
    [APPID] = DLookup("[APPID]", "UpdatedFiles", strfilapp)
    [LN] = DLookup("[LN]", "UpdatedFiles", strfilapp)
    [FN] = DLookup("[FN]", "UpdatedFiles", strfilapp)
    [PAPERAPP] = DLookup("[PAPERAPP]", "UpdatedFiles", strfilapp & "[ENTRYDT]" >= # LATEST #)
    On Error GoTo 0
Else
    Me.APPID = ""
    MsgBox ("No file with an hyperlinked paper application found for your search. Searching for a file without...")

    strcheck = DLookup("[APPID]", "InitialFiles", strfilapp)

    If Not IsNull(strcheck) Then
        On Error Resume Next
    [APPID] = DLookup("[APPID]", "InitialFiles", strfilapp)
    [LN] = DLookup("[LN]", "InitialFiles", strfilapp)
    [FN] = DLookup("[FN]", "InitialFiles", strfilapp)
        On Error GoTo 0
    Else
        Me.APPID = ""
        MsgBox ("No file found for your search. Try again.")
        Me.SearchField.SetFocus
    End If
End If

End Sub

When I perform the APPID search, I keep getting the application that was first entered into the database, versus the most current (based on ENTRYDT).

I've tried adding the ENTRYDT to the [PAPERAPP] line, as a parameter in DLookup, but just can't seem to figure out how to do it. May you please help me?

Thank you!

Solution

If you want PAPERAPP associated with most recent - as in Max() - date for given APPID, that probably needs another domain aggregate function.

[PAPERAPP] = DLookup("[PAPERAPP]", "UpdatedFiles", strfilapp & " AND [ENTRYDT] = #" & _
            DMax("ENTRYDT", "UpdatedFiles", "APPID='" & [APPID] & "'") & "#")