comparing multiple date fields would like to show the minum value

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I have three date fields date1 date2 date3. i would like to return the min
value of all three columns
 
A Union query might be a start, not sure how efficient that is though.

SELECT Min(Table1.Date1) as MinDate
FROM Table1 Union SELECT Min(Table1.Date2) as MinDate
FROM Table1 UNION SELECT Min(Table1.Date3) as MinDate
FROM Table1;
 
Michael,

You don't say how or where you want to do this. I have a function that I
use for just such occassions. It accepts as many parameters as you want to
pass it, and returns the minimum value. If you want to use this in a query,
you will have to save to the function in a code module, you would do
something like:

SELECT [Date1], [Date2], [Date3], fnMin([Date1], [Date2], [Date3]) as MinDate
FROM yourTable

Public Function fnMin(ParamArray ValList() As Variant) As Variant

Dim intLoop As Integer
Dim myVal As Variant

For intLoop = LBound(ValList) To UBound(ValList)
If Not IsNull(ValList(intLoop)) Then
If IsEmpty(myVal) Then
myVal = ValList(intLoop)
ElseIf ValList(intLoop) < myVal Then
myVal = ValList(intLoop)
End If
End If
Next
fnMin = myVal

End Function

This ignores NULL values and returns the minimum value passed to it. It is
easy to modify this for Max value as well.

HTH
Dale
 
Back
Top