Compare Date fields and return lowest 2

[kirstenlargent]

I have four separate date fields in a record. I need to do an update query to compare all 4 date fields, and add the resulting lowest date to a new field in the record.
What is the syntax I could use for that? I tried doing an if statement, but it got too big to try to compare all four dates to each other!
Thanks!

 

[dhookom]

This table structure seems un-normalized. However, I would create a user-defined function like:

Function GetMinDate(datOne as Date, datTwo as Date, _
datThree as Date, datFour as Date) as Date
Dim datMin as Date
datMin = datOne
If datTwo < datMin Then
datMin = datTwo
End If
If datThree < datMin Then
datMin = datThree
End If
If datFour < datMin Then
datMin = datFour
End If
GetMinDate = datMin
End Function

You can use this function in a query (or elsewhere).
It assumes the fields are null.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

A generic function to get the min value of an arbirtrary numbers of same type values:
Public Function myMin(ParamArray Args())
Dim i As Long, rv
rv = Args(LBound(Args))
For i = 1 + LBound(Args) To UBound(Args)
If IsNull(rv) Or rv > Args(i) Then rv = Args(i)
Next
myMin = rv
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244