Min function   

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

date: Thu, 4 Sep 2008 09:37:19 -0500   author:   Boon

RE: Min function   

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.



"Boon" wrote:

> 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
> 
> 
>

date: Thu, 4 Sep 2008 07:56:01 -0700   author:   Dale Fye