SELECT DATE:

[akasmia]

I think this one very is simple but I am getting and error:
I have 4 dates in a table:
DATE1 DATE2 DATE3 DATE4

I add nothing to DATE1, 2 years to DATE2 and DATE3 and 1 year to DATE4 then I select whichever date comes first.

I have created a form and stored the modified dates (i.e. DATE1, DATE2+2yrs, DATE3+2yrs and DATE4+1yr) in 4 different text boxes (formated as dates). I attempted to use =MIN(txtBox1,txtBox2,txtBox3, txtBox4) in the Control Sourse to store the result in a table field called EXP_DATE. I have also tried the function SMALL and no luck so far. I am trying to treat my four dates as an array of dates and pick up the date sooner date.

ANY HELP?

 

[thornmastr]

The only VB min function available is an aggregate function which will not work for you. Try this one:



'---------------------------------------------------------------------------------------
' Procedure : dhArrayMin
' DateTime : 8/13/2002 08:29
' Author : bermanr
' Purpose : Return the minimum value from an array
'---------------------------------------------------------------------------------------
'
Public Function dhArrayMin(varArray As Variant) As Variant
Dim varitem As Variant
Dim varmin As Variant
Dim i As Long
On Error GoTo dhArrayMin_Error
If IsArray(varArray) Then
If UBound(varArray) = -1 Then
dhArrayMin = Null
Else
varmin = varArray(LBound(varArray))
For i = LBound(varArray) To UBound(varArray)
varitem = varArray(i)
If varitem < varmin Then
varmin = varitem
End If
Next i
dhArrayMin = varmin
End If
Else
dhArrayMin = varArray
End If

On Error GoTo 0
Exit Function

dhArrayMin_Error:

MsgBox &quot;Error &quot; & Err.Number & &quot; (&quot; & Err.Description & &quot in procedure dhArrayMin of Module modTest&quot;

End Function
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com