Minimum Value (Date)

[AccessLearner73]

I have 6 date fields in a query record & want to create one field to access smallest date value in each record.

Please help on building expression.

[PhilOfWalton]

Bit short of information.

Are the dates all related to different things like BuyDate, SellDate or are they all referring to the same thing like Stock at a StockDate?

Phil

[AccessLearner73]

they are like buy date, sale date, delivery date & are in the same record set. I am getting this into a query & want to create a new field in query itself to get me smallest date out of all these dates.

[PhilOfWalton]

I have a table consisting of ID and Date1 to Date6

Here is the SQL for the output

SELECT Dates.*, LowestDate([ID]) AS LowDate
FROM Dates;
 

Here is a bit of code to scan the dates to find the lowest

Function LowestDate(ID As Long) As Date
 
    Dim MyDb As Database
    Dim DateSet As Recordset
    Dim LowDate As Date
 
    Set MyDb = CurrentDb
    Set DateSet = MyDb.OpenRecordset("SELECT Dates.* FROM Dates WHERE ID = " & ID & ";")
 
    With DateSet
        LowDate = !Date1
        If !Date2 < LowDate Then
            LowDate = !Date2
        End If
        If !Date3 < LowDate Then
            LowDate = !Date3
        End If
        If !Date4 < LowDate Then
            LowDate = !Date4
        End If
 
        If !Date5 < LowDate Then
            LowDate = !Date5
        End If
        If !Date6 < LowDate Then
            LowDate = !Date6
        End If
        .Close
        Set DateSet = Nothing
    End With
 
    LowestDate = LowDate
 
End Function
 

Phil