Find Maximum Date Value Across Fields of Record-Access VBA

[dar149]

I am trying to find the maximum value across Fields of a Record. I have a function that works great in a query if all fields have a value. However, if there is a null value, the function returns nothing. Does anyone have a suggestion? Following is the code:

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

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

' Return the maximum value found.
Maximum = currentVal

End Function

 

[SBendBuckeye]

The function Nz returns zero or a null string depending on the data type (eg nz(MyName) returns vbNullString or nz(Age) returns 0). Try using Nz around which ever field is sometimes null.

Good LucK!

 

[dar149]

The Nz function would work great, except that I am using date fields, and the Nz function returns a zero, which is not an acceptable date. The result is still empty because the first field doesn't contain a date. Any other suggestions?

 

[SBendBuckeye]

Have you tried something like this:

' Cycle through each value from row to find the largest.
For I = 0 To UBound(FieldArray)
If Not IsNull(FieldArray(I)) Then 'Check for Null 1st
If FieldArray(I) > currentVal Then
currentVal = FieldArray(I)
End If
End If
Next I

 

[dar149]

Thanks for the code! I did get it to work.