Min value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
Is there any function in Access working like Excel

If column1 to colulmn5 contains the numbers 10, 7, 9, 27, and 2, then:

MIN(column1, column2, column3, column4, column5) equals 2

Thanks

Ed
 
There's no built-in function for this, but paste the function below into a
standard moule. You can then use:
=Smallest([column1], [column2], [column3], [column4], [column5])

Function Smallest(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMin As Variant 'Largest value found so far.

varMin = Null 'Initialize to null

For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Then
If varMin <= varValues(i) Then
'do nothing
Else
varMin = varValues(i)
End If
End If
Next

Smallest = varMin
End Function
 
This function is now available on the web, with an explanation of how it
works:
http://members.iinet.net.au/~allenbrowne/func-09.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen Browne said:
There's no built-in function for this, but paste the function below into a
standard module.
[snip]

Ed said:
Hi,
Is there any function in Access working like Excel

If column1 to colulmn5 contains the numbers 10, 7, 9, 27, and 2, then:

MIN(column1, column2, column3, column4, column5) equals 2
Click to expand...
Click to expand...
 
For a more general solution, try:

SELECT MIN(DT1.My_col) AS my_min
FROM (
SELECT column1 AS My_col FROM MyTable
UNION
SELECT column2 AS My_col FROM MyTable
UNION
SELECT column3 AS My_col FROM MyTable
UNION
SELECT column4 AS My_col FROM MyTable
UNION
SELECT column5 AS My_col FROM MyTable
) AS DT1;

Jamie.

--
 
Back
Top