Help! Selecting the lowest value in a row

[tennisbuck]

Good Morning-

If I had a query structered like this:


Part Number Suppplier A Supplier B Supplier C Chose this supplier/Price
100 $50.00 $60.00 $55.00 ?

How could I create a column that would either chose the supplier's name or price as the lowest optioin? I know how to do conditional formatting in excel or min function but can't figure this out in Access.

Mike

[pbaldy]

The table isn't normalized, but:

Minimum or Maximum Value Across Fields of Record

[tennisbuck]

Thanks. So what do I need to change in this code?

Function Minimum(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 the row to find the smallest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
Next I

' Return the minimum value found.
Minimum = currentVal

End Function

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 the 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
Table: tblMinMax
---------------------------
Field Name: Field1
Data Type: Number
Field Size: Long Integer

Field Name: Field2
Data Type: Number
Field Size: Long Integer

Field Name: Field3
Data Type: Number
Field Size: Long Integer

[pbaldy]

Nothing, you just copy the function into a standard module and call it from wherever it's needed.

[tennisbuck]

Thanks! It worked perfect.

[pbaldy]

No problem!