Looking for the lowest value in fields from a table row

[GJP55]

I am trying to write a function that will look at certain fields from a row in a table and return the lowest value. The part I am getting stuck on is how to refer to the fields that it needs to look up.

The DMin function looks ideal but I do not know how I can use it to look at serveral fields from the same row.

Example:

MyTable has fields 1 to 10 and I would like to find the lowest value out of fields 1,2 and 3 per recordset.

Any help greatly appreciated.

Thanks

 

[young]

You did not do the same project but I think that you need multiple loop and compare function to compare one by one.

 

[GJP55]

Just found a minimum and maximum function and the Microsoft Knowledge base so its working ok.

Thanks for your time though.

If interested I got it from Q182760

Cheers

 

[PaulBricker]

Function RMin(ParamArray FieldValues()) As Variant
       Dim lngMin As Long
       Dim varArg As Variant

          lngMin = 0

       For Each varArg In FieldValues
          If varArg < lngMin Then
               lngMin = varArg
          End If
       Next
       RMin = lngMin
End Function

This one will do it(it may look a lot like the MS article you read. To use it, you will put an expression like this in your query.

MyMinValue:RMin([Field1, Field2, Field3,...Fieldn)

This will return the minimum value for the Fields.

Paul

 

[GJP55]

Paul,

Thanks for that,

GJP

 

[MichaelRed]

I would suggest that the var "lngMin" be re-typed as variant (and possibly re-named to agree with the tyoe cast). using a LONG strictly limits the (pratical) use to the specific data type.

see thread701-416122 for another version *basMinVal" which has been posted numerous times in these fora, or do a search on the procedure name ('basminval')


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GJP55]

Michael,

Thanks for your reply.
The code is very useful and I appreciate your time.

Thanks

GJP