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: