Min function

[Boon]

Hi there,

I want to use a function that finds a minimum between a list of number in
query.

I don't know what this function is, so I will call it "Func"

For example, in one column in query design view I would like to write
something like this:

Minimum: Func(4,5,6,2,9)

Then when I run the query, I would like to see "2" in column "Minimum"

Thanks a lot
Boon

 

[Dale Fye]

There is no Access function to do this. But I have one I've created and used
in the past. Copy this code and put it in a code module. Then you can call
it from your query or in code.

Public Function fnMin(ParamArray SomeValues() As Variant) As Variant

'Accepts an array of parameters, preferably of the same data type,
'but can be any type including NULL values
'fnMin(3, 7, 10) = 3
'fnMin(#9/1/07#, #9/15/07#, #10/1/06#) = #9/1/07#

Dim intLoop As Integer
Dim myMin As Variant

For intLoop = LBound(SomeValues) To UBound(SomeValues)

If IsNull(SomeValues(intLoop)) Then
'do nothing
ElseIf IsEmpty(myMin) Or (SomeValues(intLoop) < myMin) Then
myMin = SomeValues(intLoop)
End If

Next

fnMin = myMin

End Function
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.