Return the Max of 4 different Fields

[ashleybyrdnc]

In my form I have 4 different date fields, and I need to return the largest of those date in another field. I have tried the Max() but it doesn't work with multi fields, any suggestions?

Thanks

 

[MajP]

Public Function maxDate(ParamArray dtmDates() As Variant) As Date
Dim counter As Integer
For counter = LBound(dtmDates) To UBound(dtmDates)
If CDate(Nz(dtmDates(counter))) > maxDate Then
maxDate = dtmDates(counter)
End If
Next counter
End Function

 

[Golom]

Here's a function that I use

'-----------------------------------------------------------
' Procedure : MaxVal
' Purpose   : Return the maximum value of the supplied values
' Arguments : Vals()        - Array of Values
'-----------------------------------------------------------
'
Public Function MaxVal(ParamArray Vals() As Variant) As Variant
    Dim x                           As Variant
    Dim MV                          As Variant
    MV = Vals(0)
    For Each x In Vals
        If Not IsNull(x) Then
            If IsNull(MV) Then
                MV = x
            ElseIf x > MV Then
                MV = x
            End If
        End If
    Next
    MaxVal = MV

End Function

Use it as

MaxDate = MaxVal(Date1, Date2, Date3, Date4)

 

[PHV]

I've already posted this:

'A generic function to get the max value of an arbirtrary numbers of same type values:
Public Function myMax(ParamArray Args())
Dim i As Long, rv
For i = 0 To UBound(Args)
  If IsNull(rv) Or rv < Args(i) Then rv = Args(i)
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry for the truncated post.

'A generic function to get the max value of an arbirtrary numbers of same type values:
Public Function myMax(ParamArray Args())
Dim i As Long, rv
For i = 0 To UBound(Args)
  If IsNull(rv) Or rv < Args(i) Then rv = Args(i)
Next
myMax = rv
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ashleybyrdnc]

Golom when I tried it it gives me a #name? error. I put the function in there then as the data source I have
=MaxVal([LRN_Info]![Date_1],[LRN_Info]![Date_2],[LRN_Info]![Date_3],[LRN_Info]![Date4],[LRN_Info]![Date_5])

What am I doing wrong?

 

[Golom]

I suspect your arguments.

Test MaxVal in the immediate window with

? MaxVal ( 1, 99, NULL, 4, 5 )

If that returns "99" then MaxVal is working.

I do notice that you have [Date_1], [Date_2], etc., but the fourth argument is Date4 not Date[red]_[/red]4.

The underscore is missing.

 

[ashleybyrdnc]

It works with # but not with my dates. Should it work for dates?

 

[Golom]

It should work for any datatype except objects.

Did you correct "Date4" to "Date_4"?

 

[ashleybyrdnc]

It works partly, it doesn't recalculate if I make a change to the last date in the Parameters. Or it doesn't automatically I have to close the form them open it back up. Any suggestions?