Display earliest date from several fields (1 Viewer)

[smally]

Hi, I need to use a function that works out the earliest date from different fields.

I'm currently using the following code provided from a Microsoft webpage:

Function Minimum(ParamArray FieldArray() As Variant)
   ' Declare the two local variables.
   Dim i As Integer
   Dim currentVal As Variant

   ' Set the variable currentVal equal to the array of values.
   currentVal = FieldArray(0)

   ' Cycle through each value from the row to find the smallest.
   For i = 0 To UBound(FieldArray)
      If FieldArray(i) < currentVal Then
         currentVal = FieldArray(i)
      End If
   Next i

   ' Return the minimum value found.
   Minimum = currentVal

End Function

The problem I'm having is I need to show the earliest date, but some fields can be null. So if the 1st parameter is null, the result is null even though there is a date in the second parameter.

I need it to show the earliest date even if the 1, 2 or 3 parameters are null, and the result to be null if all parameters are null.

 

[The_Doc_Man]

   For i = 0 To UBound(FieldArray)
      If not IsNull( FieldArray(i)) Then
          If FieldArray(i) < currentVal Then
             currentVal = FieldArray(i)
          End If
      End If
   Next i

However, this doesn't address the case where all of your fields are null.
That would be on YOU to determine a default value or a method of "escaping" the situation altogether.

 

[smally]

Still not working.
If parameter 1 is null, and 2 is not null, the result is null.

 

[static]

Function Minimum(ParamArray FieldArray() As Variant)
    Dim v As Variant
    Minimum = Null
    
    ' Cycle through each value from the row to find the smallest.
    For Each v In FieldArray
         If IsNull(Minimum) Then
             Minimum = v
         Else
             If Not IsNull(v) Then
                 If v < Minimum Then
                     Minimum = v
                 End If
             End If
         End If
    Next

End Function

 

[smally]

Thanks, that works perfectly.