Min of 5 columns function

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

--
Thanks in advance,

RT_Indy

On Jan 23, 2:07*pm, RT_Indy wrote:
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

--
Thanks in advance,

RT_Indy

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;

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/...UBED1=ACCESS-L


"RT_Indy" wrote in message
...
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

--
Thanks in advance,

RT_Indy

On Fri, 23 Jan 2009 12:07:00 -0800, RT_Indy
wrote:

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.
--

John W. Vinson [MVP]