Calculate min value from multiple fields for one record in Access 2003

Hi,
I thought this would be an easy task, but need to ask for help.  I need to calculate minimum value of multiple fields for one record (one record has multiple contract terms and I need to calculate the minimum in a query in order to report on it).  In Excel I would just use =MIN(field1, field1, field3), but apparently the MIN function in Access/SQL finds the minimum of multiple records within one field.
How do I compare fields within one record to find the minimum in Access?
Thanks!
chrisandbertoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Hello chrisandberto,

Try adding this function to your project:



Function GetMin(ParamArray args()) As Double

    Dim x As Variant
   
    For Each x In args
        If IsNumeric(x) Then
            If x < GetMin Then GetMin = x
        End If
    Next
   
End Function


Then use it like this:

SELECT *, GetMin(field1, field2, field3, field4, field5) AS TheMinimum
FROM SomeTable

Regards,

matthewspatrick
0
chrisandbertoAuthor Commented:
Thank you so much for the prompt suggestion.  I added the function and tried the query, but I am getting all zeros, even when all fields have a number.  (And I tried changing the fields size to integer or double...it was byte).  Another thing to mention...often multiples of these fields will be null.

Other suggestions?
0
Patrick MatthewsCommented:
chrisandberto,

This fixes the problem, and skips nulls.  If all the fields are null, it returns 0.

Function GetMin(ParamArray args()) As Double

    Dim x As Variant
    Dim First As Boolean
   
    First = True
   
    For Each x In args
        If IsNumeric(x) Then
            If First Then
                GetMin = x
                First = False
            Else
                If x < GetMin Then GetMin = x
            End If
        End If
    Next
       
End Function

matthewspatrick
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Question:
How many fields are there ... and are they all the same data type?

mx
0
chrisandbertoAuthor Commented:
That did it...thank you!!!

MX, there are 4 fields, and they are all number data types.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
fwiw ... I was going to suggest a Union query ... union those 4 fields into one .... then, all you would need would be one more query to Group ... and do a Min on that combined field.  No code.  Oh well ...

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.