Finding the lowest value in multiple fields. Issue with Null

[Southsider]

I am trying to find the earliest date out of 6 different date fields (some of which may be null). I found this article on the Microsoft Site (support.microsoft.com/kb/182760) that helped me create a module so I have a function called Minimum and Maximum that seems very easy to use, and in fact it does work except for one BIG problem. If one of the fields it's looking at is Null, the calculated field also becomes null. I want it to ignore the null fields and pick the lowest value of the fields that aren't null. I'm not sure how to do this. I'm guessing I have to edit the module somehow. Anyway, here's the formula for my calculated field:

First Result Date: Minimum([Date1],[Date2],[Date3],[Date4],[Date5],[Date6])

I think the other option is to make an elaborate If statement, based on something like this:
Iif([fieldA]<[FieldB],[fieldA],[fieldB])
IIf(((IIf([fieldA]<[fieldB],[fieldA],[fieldB])))<[fieldc],((IIf([fieldA]<[fieldB],[fieldA],[fieldB]))),[fieldc])

Obviously that's a few fields short. I guess I'd prefer to use the first solution if possible.

Thanks for any help, I greatly appreciate it.

 

[pbaldy]

Presumably you're using something like this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209857

which is easy to modify to exclude Null values. Just add a test like:

If Not IsNull(FieldArray(I)) Then

 

[Southsider]

Thank you!

 

[pbaldy]

No problem!

 

[Southsider]

which is easy to modify to exclude Null values. Just add a test like:

If Not IsNull(FieldArray(I)) Then

OK, actually I didn't get that to work. I have Maximum working because I found help elsewhere that said to put Nz around FieldArray(I). I've tried putting the If statment you mentioned in a couple of different places but I can't get it to work.
Here's my current code--

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
       If Not IsNull(FieldArray(I)) Then
         currentVal = FieldArray(I)
       End If
      End If
   Next I

   ' Return the minimum value found.
   Minimum = currentVal

End Function

Function Maximum(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 = Nz(FieldArray(0))

   ' Cycle through each value from the row to find the largest.

   For I = 0 To UBound(FieldArray)
      If Nz(FieldArray(I)) > currentVal Then
         currentVal = Nz(FieldArray(I))
      End If
   Next I

   ' Return the maximum value found.
   Maximum = currentVal

End Function

Thanks for any help!

 

[pbaldy]

Try

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

 

[Southsider]

Hmm, that didn't work. It still generates a null field if any of the fields it looks at are null. I want it to pick the lowest of the fields that has a value in it. Here's my code now--

Option Compare Database

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 Not IsNull(FieldArray(I)) Then
    If FieldArray(I) < currentVal Then
         currentVal = FieldArray(I)
    End If
  End If
Next I
   ' Return the minimum value found.
   Minimum = currentVal

End Function

Function Maximum(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 = Nz(FieldArray(0))

   ' Cycle through each value from the row to find the largest.

   For I = 0 To UBound(FieldArray)
      If Nz(FieldArray(I)) > currentVal Then
         currentVal = Nz(FieldArray(I))
      End If
   Next I

   ' Return the maximum value found.
   Maximum = currentVal

End Function

 

[pbaldy]

They must not be Null. Try

If Len(FieldArray(I) & vbNullString) > 0 Then

 

[Southsider]

Hmm, that didn't work either. Thanks for the input though, I appreciate it. This was my code--

Option Compare Database

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 Len(FieldArray(I) & vbNullString) > 0 Then
    If FieldArray(I) < currentVal Then
         currentVal = FieldArray(I)
    End If
  End If
Next I
   ' Return the minimum value found.
   Minimum = currentVal

End Function

Function Maximum(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 = Nz(FieldArray(0))

   ' Cycle through each value from the row to find the largest.

   For I = 0 To UBound(FieldArray)
      If Nz(FieldArray(I)) > currentVal Then
         currentVal = Nz(FieldArray(I))
      End If
   Next I

   ' Return the maximum value found.
   Maximum = currentVal

End Function

They must not be Null. Try

If Len(FieldArray(I) & vbNullString) > 0 Then

 

[pbaldy]

Can you post the db here?

 

[Southsider]

No, I can't because it's massive and has confidential customer info. There are like 700,000 records it has to go through, I think. I wish I could, sorry. I doubt this will help but here's the actual calculated field formula--

First UW Result Date: Minimum([DATE_FINAL_APPROVAL],[Date_Approved],[DATE_COND_APPR_FIRST],[DATE_CREDIT_SUSPENDED_1ST_TIME],[CDF93 DATE-CREDIT DENIED UW],[Date_Cancelled])

Can you post the db here?

One of these actions has to take place. I've confirmed that I'm getting blanks in the calculated field when at least one of these is present, but some of the others are not.

 

[pbaldy]

I'm confused, because that line should make it skip empty fields. Since they're all dates, try

If IsDate(FieldArray(I)) Then

If that doesn't work, maybe you can extract a few records and the function and query into an new db?

 

[Southsider]

I'm confused, because that line should make it skip empty fields. Since they're all dates, try

If IsDate(FieldArray(I)) Then

If that doesn't work, maybe you can extract a few records and the function and query into an new db?

Well, that didn't work either. I may end up having to make a new DB to show you what I'm working with, but I don't think I can include the linked tables I'm querying from, so the query is just code. One thing-- when I run the query it asks me to enter a Parameter Value for First UW Result Date and Last UW Result Date. Would this make a difference? It still seems to calculate Last UW Result Date 100%, and all of First UW Result date except for this one issue.

 

[Southsider]

I found another function that somebody had created online that worked. Here's the code FYI.

Function fMin2(ParamArray varValues() As Variant) As Variant

    Dim I As Integer, vMin As Variant
    vMin = varValues(0)
    For I = 1 To UBound(varValues())
        If IsNull(vMin) Then
            vMin = varValues(I)
        ElseIf varValues(I) < vMin Then
            vMin = varValues(I)
        End If
    Next I
    fMin2 = vMin

End Function