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:
