I just ran the code in post #23. When I place the expression into the query I receive, error wrong number of arguments:
Expr1: Minimum([nPP1SHF],[nPP2SHF],[nPP3SHF],[nPP4SHF],[nPP5SHF],[nPP6SHF],[nPP7SHF],[nPP8SHF],[nPP9SHF],[nPP0SHF])
liamthequietman
Last Activity: May 26 '19, 09:34 PM
Joined: Mar 3 '18
Location:
-
liamthequietman replied to Access Maximum Function: Return the second highest value across 10 fieldsin AccessI found a code which works perfect EXCEPT ...
If there is a column(s) with a -1 it is returning the lowest number, not the second lowest.
Otherwise all the result are correct.
...Code:Function SecondMinimum(ParamArray FieldArray() As Variant) As Variant Dim I As Integer Dim LowestVal As Variant Dim secondVal As Variant ' Default values LowestValLeave a comment:
-
liamthequietman replied to Access Maximum Function: Return the second highest value across 10 fieldsin AccessI applaud you for your tenacity! The expression I enter into the query has worked in all your codes except now. Error wrong number of arguments:
Expr1: Minimum([nPP1SHF],[nPP2SHF],[nPP3SHF],[nPP4SHF],[nPP5SHF],[nPP6SHF],[nPP7SHF],[nPP8SHF],[nPP9SHF],[nPP0SHF])Leave a comment:
-
liamthequietman replied to Access Maximum Function: Return the second highest value across 10 fieldsin AccessCan I attach a test version of my database?Leave a comment:
-
liamthequietman replied to Access Maximum Function: Return the second highest value across 10 fieldsin AccessThanks for not giving up on this project!
My step through attempt did not work, a Macro box appears (see pic).
I found another issue with the results. In review:
CORRECT
Columns without -1 are returning the correct second lowest number
Columns with all -1 returns -1 which is perfect
INCORRECT
If there is a column(s) with a -1 it is returning...Leave a comment:
-
liamthequietman replied to Access Maximum Function: Return the second highest value across 10 fieldsin AccessBeing a complete VBA novice, I'm not familiar on how to step through the code. I placed my cursor in the code and pressed F8 and received a ping which I associate with an error.
Declaring the array as Double resulted in a errorLeave a comment:
-
liamthequietman replied to Access Maximum Function: Return the second highest value across 10 fieldsin AccessIf there is a column(s) with a -1 it is returning the lowest number, not the second lowest.
Columns without -1 are returning the correct second lowest number
Columns with all -1 returns -1 which is perfect...Leave a comment:
-
liamthequietman replied to Access Maximum Function: Return the second highest value across 10 fieldsin AccessMy changes to the code work perfect except if a -1 appears in any column.
see pic in post I editedLeave a comment:
-
liamthequietman replied to Access Maximum Function: Return the second highest value across 10 fieldsin AccessI'm a novice and will now Google "Code Tags" to understand what they are.
My apologies!Leave a comment:
-
liamthequietman replied to Access Maximum Function: Return the second highest value across 10 fieldsin AccessI edited my post. Please read again I almost have the solution!
Thanks!Leave a comment:
-
liamthequietman replied to Access Maximum Function: Return the second highest value across 10 fieldsin AccessI've made changes to the code for "Minimum" values "Second Lowest" The values are Single and contain -1
It works perfect except if a -1 appears in any of the columns.
Greatly appreciated!
(see pic)
...Code:Function Minimum(ParamArray FieldArray() As Variant) On Error GoTo EH ' Declare the two local variables. Dim I As Integer Dim currentValLeave a comment:
-
liamthequietman replied to Access Maximum Function: Return the second highest value across 10 fieldsin AccessBRILLIANT!
What an excellent collaboration between the two of you!
I hope others can benefit from this excellent code.
I'm very grateful!Leave a comment:
-
liamthequietman started a topic Access Maximum Function: Return the second highest value across 10 fieldsin AccessAccess Maximum Function: Return the second highest value across 10 fields
The code works great except if the first field is a zero or all the fields in the row contain zeros (see pic).
Many thanks!
...Code:Function Maximum(ParamArray FieldArray() As Variant) ' Declare the two local variables. Dim I As Integer Dim currentVal As Variant Dim secondHighest As Variant ' Set the variable currentVal equal to the array of values. currentVal = FieldArray(0)
No activity results to display
Leave a comment: