Solved Get the latest date from 3 fields

[Number11]

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

Phone	Email	Text	Latest Contact
12/05/2023​	21/10/2023​		21/10/2023​
	01/11/2023	31/10/2023	01/11/2023

 

[pbaldy]

As noted in the link, your design is flawed, but:

Microsoft Access tips: MaxOfList() and MinOfList() functions

Code for selecting the minimum and maximum values of a list of values in a Microsoft Access database. Demonstrates ParamArray.

allenbrowne.com

 

[Number11]

Ok thanks got that all working now using that suggested link method Happy days!

 

[GPGeorge]

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

Phone	Email	Text	Latest Contact
12/05/2023​	21/10/2023​		21/10/2023​
	01/11/2023	31/10/2023	01/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.

 

[Eugene-LS]

to get the latest date from three possible fields

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