Posts Tagged ‘lowest’

Finding the lowest non-zero value of multiple columns in Microsoft Access

Programming | Posted by epistasis
Jul 01 2014

Recently (for work) I wanted to find the lowest non-zero value of multiple columns in Microsoft Access. I had to trawl the Internet for the best way to do this and in the end worked with a mixture of several methods from different websites. I hope that this post will serve as a single point of reference for anybody wanting to do the same thing.

In this example I will use the following table:

003

 

1) First you need to open the VB editor:

001

2) Create a new module:

002

3) Paste in this code:

Function MinColumn(ParamArray FieldArray() As Variant)

Dim I As Integer
Dim currentVal As Variant

currentVal = FieldArray(0)

'Loop through each value in the columns
For I = 0 To UBound(FieldArray)
    'If the the column we're checking is lower than the last (and non-zero), take it
    If FieldArray(I) < currentVal And FieldArray(I) <> 0 Then
        currentVal = FieldArray(I)
    End If
    
    'If the current value is zero and we find a higher value, take it
    If currentVal = 0 And FieldArray(I) Then
        currentVal = FieldArray(I)
    End If
Next I

MinColumn = currentVal

End Function

4) Close the VB editor

5) Create this query:

SELECT EmployeeLastName,
MinColumn(EmployeeRate1, EmployeeRate2, EmployeeRate3, EmployeeRate4) AS LowestRate
FROM Employee

You should end up with the following results:

004