Min of 5 columns function

[RT_Indy]

I am in need of some help with a function. I have 5 columns, with each of
the 5 being a date field. I need a function that will return the minimum date
of the 5. I have found code on this board that works beautifully with 3
columns, but I don't understand VB programming/logic and haven't been
successful modifying the code I found (posted below) to work for 5 columns.
Is there anyone that can help me out? It seems simple for someone who
understand VB - but unfortunately that is not me.

Public Function MyMin(dte1 As Date, dte2 As Date, dte3 As Date) As Date
If dte1 <= dte2 Then
If dte1 <= dte3 Then
MyMin = dte1
End If
Else
If dte2 <= dte3 Then
MyMin = dte2
Else: MyMin = dte3
End If
End If
End Function

 

[pietlinden]

I am in need of some help with a function.  I have 5 columns, with eachof
the 5 being a date field. I need a function that will return the minimum date
of the 5.  I have found code on this board that works beautifully with 3
columns, but I don't understand VB programming/logic and haven't been
successful modifying the code I found (posted below) to work for 5 columns.  
Is there anyone that can help me out?  It seems simple for someone who
understand VB - but unfortunately that is not me.

Public Function MyMin(dte1 As Date, dte2 As Date, dte3 As Date) As Date
  If dte1 <= dte2 Then
    If dte1 <= dte3 Then
      MyMin = dte1
    End If
  Else
    If dte2 <= dte3 Then
      MyMin = dte2
    Else: MyMin = dte3
    End If
  End If
End Function

bad design
union all the columns together, and then use the MIN() function
SELECT SomeField, DateField1 As SomeDate FROM MyTable
UNION ALL
SELECT SomeField, DateField2 As SomeDate FROM MyTable
UNION ALL
SELECT SomeField, DateField3 As SomeDate FROM MyTable;

(etc)


then
SELECT SomeField, MAX(SomeDate)
FROM MyTable
GROUP BY SomeField;

 

[Roger Carlson]

Well, the real problem is your database is designed improperly. Instead of
having one record with multiple dates, you should have one date field and
multiple records. Then you could use the SQL Min function.

However, if you're not in a position to change the data structure (although
I heartily recommend it), the following function should work:

Public Function MyMin(dte1 As Date, dte2 As Date, dte3 As Date, dte4 As
Date, dte5 As Date) As Date
Dim arrDate(5) As Date
Dim i As Integer
Dim j As Integer
Dim Temp As Date

arrDate(1) = dte1
arrDate(2) = dte2
arrDate(3) = dte3
arrDate(4) = dte4
arrDate(5) = dte5
MyMin = dte1
For i = 1 To 5
For j = i To 5

If arrDate(i) < arrDate(j) Then
Temp = arrDate(i)
Else
Temp = arrDate(j)
End If

If Temp < MyMin Then
MyMin = Temp
End If
Next j
Next i

End Function


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com

Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John W. Vinson]

I am in need of some help with a function. I have 5 columns, with each of
the 5 being a date field. I need a function that will return the minimum date
of the 5. I have found code on this board that works beautifully with 3
columns, but I don't understand VB programming/logic and haven't been
successful modifying the code I found (posted below) to work for 5 columns.
Is there anyone that can help me out? It seems simple for someone who
understand VB - but unfortunately that is not me.

Public Function MyMin(dte1 As Date, dte2 As Date, dte3 As Date) As Date
If dte1 <= dte2 Then
If dte1 <= dte3 Then
MyMin = dte1
End If
Else
If dte2 <= dte3 Then
MyMin = dte2
Else: MyMin = dte3
End If
End If
End Function

I fully agree with Roger that your table structure is WRONG and needs to be
corrected... but here's a little routine I wrote for another poster that will
find the minimum of any arbitrary number of values:

Public Function GetMinimum(ParamArray vX() As Variant) As Variant
Dim iPos As Integer
GetMinimum = #12/31/9999# 'largest possible date
For iPos = 0 To UBound(vX())
If vX(iPos) < GetMinimum Then GetMinimum = vX(iPos)
Next iPos
End Function

Call it using

Earliest: GetMinimum([Date1],[Date2],[Date3],[Date4],[Date5])

Using Variant for the array type is essential to use ParamArray, but also
allows you to pass NULL values for the date.

That said... *normalize your data*. Someday you'll need *six* dates and your
entire database will need to be restructured.