comparing multiple date fields would like to show the minum value

[Michael]

I have three date fields date1 date2 date3. i would like to return the min
value of all three columns

 

[Ralph]

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;

 

[Dale Fye]

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

 

[Alex Dybenko]

Hi,
you can also extend this function:
http://accessblog.net/2006/04/min-and-max-functions.html

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com