MS Access 2003 how to do a max - min calculation in a form using multiple fields

0
votes

I have a form with multiple fields. I have a field that I need to find the max and min of the numbers entered and subtract the min from the max.

Basically, the user will input data into the form:

  • field1 = 5
  • field2 = 4
  • field3 = 2
  • field4 = 1

  • field5 = 3

    fieldcalc = (max(field1, field2, field3, field4, field5)) - (min(field1, field2, field3, field4, field5))

should return 4

How do I implement this?

1
ms-accessms-access-2003

1 Answers

0
votes

You can write a function like this:

Public Function ReturnMinOrMax(intMinOrMax As Byte, ParamArray vals()) As Long
'intMinOrMax: 0 for min, non-0 for max

    Dim v As Variant
    Dim i As Variant

    i = vbNull

    For Each v In vals
        If IsNull(v) = False Then
            If IsNull(i) = True Then i = v

            Select Case intMinOrMax
                Case 0
                    If v < i Then i = v
                Case Else
                    If v > i Then i = v
            End Select
        End If
    Next

    ReturnMinOrMax = CLng(i)

End Function

And call it like this:

fieldcalc = ReturnMinOrMax(1, field1, field2, field3, field4, field5) - ReturnMinOrMax(0, field1, field2, field3, field4, field5)

The function takes a flag to return min or max as well as a ParamArray which you can pass as many fields as you want. Just pass it all your values and perform the subtraction operation.