Return Minimum of several fields

  • Thread starter Thread starter Gary McCarthy
  • Start date Start date
G

Gary McCarthy

I have a form where the user enters different quotes for the same service.
The quotes are from different carriers. Is there a way to pull the minimum
value from the three fields and push it to a "least cost" field?

For example, for three carriers, Carrier1 = $300, Carrier2 = $100, Carrier3
= $200

I would like the value for Carrier2 to be pushed to field LowestCost.

Thanks.
 
Gary McCarthy said:
I have a form where the user enters different quotes for the same service.
The quotes are from different carriers. Is there a way to pull the
minimum
value from the three fields and push it to a "least cost" field?

For example, for three carriers, Carrier1 = $300, Carrier2 = $100,
Carrier3
= $200

I would like the value for Carrier2 to be pushed to field LowestCost.
Click to expand...


This would be a lot simpler and more efficient if you didn't store quotes in
multuiple fields, but rather in multiple records of a related table.
However, you can use a function like this:

'------ start of code ------
Function MinInList(ParamArray ListValue() As Variant) As Variant

Dim I As Long
Dim MinVal As Variant
Dim ListVal As Variant

MinVal = Null

For I = LBound(ListValue) To UBound(ListValue)

ListVal = ListValue(I)
If Not IsNull(ListVal) Then
If IsNull(MinVal) Then
MinVal = ListVal
Else
If ListVal < MinVal Then
MinVal = ListVal
End If
End If
End If

Next I

MinInList = MinVal

End Function
'------ end of code ------

Then you could write something like:

LowestCost = MinInList(Carrier1, Carrier2, Carrier3)
 
Dirk,

Thanks. I will work on your first suggestion.

For now, in this code, would ListValue be my field name? If so, would it
read -

For I = LBound(Carrier1) to UBound (Carrier2) ?
 
Gary McCarthy said:
Dirk,

Thanks. I will work on your first suggestion.

For now, in this code, would ListValue be my field name? If so, would it
read -

For I = LBound(Carrier1) to UBound (Carrier2) ?
Click to expand...


No, you don't need to modify that code in any way. That function receives
as a parameter a list of values, and that parameter is known to the function
as ListValue, an array. Your actual field names only get used when you call
the function, as in the example call I posted:

LowestCost = MinInList(Carrier1, Carrier2, Carrier3)
 
Back
Top