Max function in VBA?

[TyroneB]

I guess there's no Max (or Min) function in VBA. Is there a simpler way to do the following, which would greatly benefit from a max function?

Thanks.

Option Compare Database
Option Explicit

Public Function PF(Distance_Feet As Variant, HF1Adj As Variant, HF2Adj As Variant, HF3Adj As Variant, HF4Adj As Variant, HF5Adj As Variant, HFFinalAdj As Variant) As Variant

Dim A As Variant
Dim B As Variant
Dim C As Variant
Dim D As Variant
Dim E As Variant
Dim F As Variant
Dim AF As Variant
Dim FA As Variant
Dim FB As Variant
Dim BF As Variant
Dim AB As Variant
Dim BA As Variant

A = HF1Adj
B = HF2Adj
C = HF3Adj
D = HF4Adj
E = HF5Adj
F = HFFinalAdj
AF = (A - F) + 95
FA = (F - A) + 95
BF = (B - F) + 95
FB = (F - B) + 95
AB = (A - B) + 95
BA = (B - A) + 95


If Distance_Feet < 2310 Then
        If A > AF And A > FA Then
            PF = A
        ElseIf AF > A And AF > FA Then
            PF = AF
        Else: PF = FA
        End If
        
ElseIf Distance_Feet >= 2310 And Distance_Feet <= 3465 Then
        If A > B And A > AF And A > FA And A > BF And A > FB And A > BA And A > AB Then
            PF = A
        ElseIf B > A And B > AF And B > FA And B > BF And B > FB And B > BA And B > AB Then
            PF = B
        ElseIf AB > A And AB > AF And AB > FA And AB > BF And AB > FB And AB > BA And AB > B Then
            PF = AB
        ElseIf BA > A And BA > AF And BA > FA And BA > BF And BA > FB And BA > AB And BA > B Then
            PF = BA
        ElseIf FB > A And FB > AF And FB > FA And FB > BF And FB > BA And FB > AB And FB > B Then
            PF = FB
        ElseIf BF > A And BF > AF And BF > FA And BF > FB And BF > BA And BF > AB And BF > B Then
            PF = BF
        ElseIf AF > A And AF > BF And AF > FA And AF > FB And AF > BA And AF > AB And AF > B Then
            PF = AF
        ElseIf FA > A And FA > BF And FA > FA And AF > FB And FA > BA And FA > AB And FA > B Then
            PF = FA
        
End If
End If

End Function

 

[llkhoutx]

It's there, search the Access Help for "functions."

 

[TyroneB]

Thanks.

I'm looking for something that does this (in vba):

max(number1, number2, number3, etc)

But it appears that the Access max does this:

max(field1)

It doesn't even do this: max(field1, field2, field3)

Is this correct?

 

[llkhoutx]

Literally, yes, however the DMAX function's artgument can be a complex expression.

 

[TyroneB]

If found this function, which seems to do the trick, here:

http://groups.google.com/group/comp...nk=gst&q=max+function&rnum=1#187ae6b7650837a7

Public Function ListMax(ParamArray ListItems() As Variant)

        Dim I As Integer

        For I = 0 To UBound(ListItems())
            If ListItems(I) > ListMax Then ListMax = ListItems(I)
        Next I

End Function

 

[Pat Hartman]

Rather than creating arrays, you should be working with aggregate functions in queries. If your tables are not normalized, you will need to normalize them to be able to use queries to do your aggregation.