epistasis.co.uk

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:

1) First you need to open the VB editor:

2) Create a new module:

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:

Tags: access, columns, lowest, microsoft, non-zero, vb, vba
No Comments »