Solved Get the latest date from 3 fields (1 Viewer)

Number11

Member
Joined
Jan 29, 2020
Messages
610
So i now need to have some code to get the latest date from three possible fields

Field names are
Phone Email Text, this is what i am looking for as the outcome

PhoneEmailTextLatest Contact
12/05/2023​
21/10/2023​
21/10/2023​
01/11/202331/10/202301/11/2023
 
Ok thanks got that all working now using that suggested link method Happy days!
 
So i now need to have some code to get the latest date from three possible fields

Field names are
Phone Email Text, this is what i am looking for as the outcome

PhoneEmailTextLatest Contact
12/05/2023​
21/10/2023​
21/10/2023​
01/11/202331/10/202301/11/2023
When you implement one work-around to compensate for an inappropriate table design, as you've now done here, you set the stage for repeated code workarounds in other places. I refer to this practice as "code wadding". It refers to the fact that each new bit of code is stuck on top of the last previous bit of code, until there is a large wad of code work-arounds in the accdb.

It's possible, of course, to continue to do that. Sometimes you inherit a flawed design and have to make it work because you don't have the opportunity to correct it. That's a fact of life. But there are a lot of reasons to correct the inappropriate design when you can, rather than stick another bit of compensatory code on it.
 
to get the latest date from three possible fields
Code:
Public Function GetLatestDate(Optional sWhereCondition As String = "")
'Usage:
'   ?GetLatestDate("RecID = 23")
'---------------------------------------------------------------------------------------------------
' Name of table with fields "Phone", "Email" & "Text" ("Text" is reserved word !!!)
Const csTableName = "Your Table Name"
Const csTextFieldName = "TText"     ' Your [Text] field  Name ("Text" is reserved word !!!)

Dim sVal$, sFromAndWhere$
Dim rst As DAO.Recordset
' -------------------------------------------------------------------------------------------------/
On Error GoTo GetLatestDate_Err
    
    sFromAndWhere = " FROM [" & csTableName & "]"
    If Len(sWhereCondition) > 0 Then
        sFromAndWhere = sFromAndWhere & " WHERE (" & sWhereCondition & ")"
    End If
    
    sVal = "Select TOP 1 * FROM (" & vbCrLf & _
            "   SELECT Phone AS LatestDate" & sFromAndWhere & vbCrLf & _
            "   UNION ALL " & vbCrLf & _
            "   SELECT Email" & sFromAndWhere & vbCrLf & _
            "   UNION ALL " & vbCrLf & _
            "   SELECT  [" & csTextFieldName & "]" & sFromAndWhere & vbCrLf & _
            ") AS SubQ ORDER BY LatestDate DESC;"
    
    Set rst = CurrentDb.OpenRecordset(sVal, dbReadOnly)
    GetLatestDate = rst.Fields(0)

GetLatestDate_End:
    On Error Resume Next
    rst.Close: Set rst = Nothing
    Err.Clear
    Exit Function

GetLatestDate_Err:
    Err.Clear
    Resume GetLatestDate_End
End Function
 

Users who are viewing this thread

Top Bottom