Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2007-04-10
Medium Priority
340 Views
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!
0
Question by:chrisandberto
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2
• 2

LVL 93

Expert Comment

ID: 18884621
Hello chrisandberto,

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

Author Comment

ID: 18884785
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

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 18884917
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

LVL 75

Expert Comment

ID: 18885315
Question:
How many fields are there ... and are they all the same data type?

mx
0

Author Comment

ID: 18885336
That did it...thank you!!!

MX, there are 4 fields, and they are all number data types.
0

LVL 75

Expert Comment

ID: 18885464
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
###### Suggested Courses
Course of the Month8 days, 2 hours left to enroll