How can I compair 3 fields and grab the smallest value.
These fields my have letters in them. The fields with
letters will need to be ignored.
Whew. This will need some VBA. See below for a suggestion!
Ok, here's a try: copy and paste this function into a new Module, and
save it as Module1 or Utilities or any other name than FindLeast. You
can then use
Least: FindLeast([Field1], [Field2], [Field3])
in a Query to return the smallest (with any number of fields):
Public Function FindLeast(ParamArray vIn() As Variant) As Variant
Dim i As Integer
Dim Least as Long ' assuming your numbers are Long Integers
FindLeast = Null ' return nothing if no numbers found
Least = 2147483647
For i = 0 to UBound(vIn()) ' loop through array
If IsNumeric(vIn(i)) Then
If vIn(i) < Least Then
Least = vIn(i)
FindLeast = vIn(i)
End If
End If
Next i
End Function
If your numbers have fractional portions, use
Dim Least as Double
Least = 1.0E308
