Max of multiple columns (1 Viewer)

Status
Not open for further replies.

Poppa Smurf

Registered User.
Local time
Today, 22:50
Joined
Mar 21, 2008
Messages
448
Khalil

Using part of the code your minimum function as per previous post.

' Cycle through each value from the row to find the smallest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
Next I

I want to find the minimum number except zero and maximum number

To exclude 0 from the test we need to add an IF END IF statement to check for a zero in the FieldArray. This is done by adding IF FieldArray(I) > 0 then and END IF as ahown in the code below.

If the value in the FieldArray(I) is greater than 0 then the next 3 lines code are processed.
If the value in the FieldArray(I) is equal to 0 then the next thrre lines are not processed.
For I = 0 To UBound(FieldArray)
IF FieldArray(I) > 0 then
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
END IF
Next I
 

Khalil Islamzada

Registered User.
Local time
Today, 16:20
Joined
Jul 16, 2012
Messages
49
Dear friend Poppa Smurf:

Thank u so much it works, very well.



Khalil



Khalil

Using part of the code your minimum function as per previous post.

' Cycle through each value from the row to find the smallest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
Next I



To exclude 0 from the test we need to add an IF END IF statement to check for a zero in the FieldArray. This is done by adding IF FieldArray(I) > 0 then and END IF as ahown in the code below.

If the value in the FieldArray(I) is greater than 0 then the next 3 lines code are processed.
If the value in the FieldArray(I) is equal to 0 then the next thrre lines are not processed.
For I = 0 To UBound(FieldArray)
IF FieldArray(I) > 0 then
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
END IF
Next I
 

natashaP

New member
Local time
Today, 04:50
Joined
Apr 25, 2013
Messages
1
Hi Poppa Smurf

I'm still getting "0" after using the suggested code
To exclude 0 from the test we need to add an IF END IF statement to check for a zero in the FieldArray. This is done by adding IF FieldArray(I) > 0 then and END IF as ahown in the code below.

If the value in the FieldArray(I) is greater than 0 then the next 3 lines code are processed.
If the value in the FieldArray(I) is equal to 0 then the next thrre lines are not processed.
For I = 0 To UBound(FieldArray)
IF FieldArray(I) > 0 then
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
END IF
Next I

Please help . Thank you !
 

Poppa Smurf

Registered User.
Local time
Today, 22:50
Joined
Mar 21, 2008
Messages
448
Hello Natasha

You will get a code if you have a record with a value of 0 in your data OR the currentVal is not set. See an earlier message where the currentval is set in the code.

I have attached a sample database that will give a minimum value.

If you are still having problems please post a sample database with you code to the forum.
 

Attachments

  • natasha.accdb
    404 KB · Views: 199

sanket91000

New member
Local time
Today, 04:50
Joined
Nov 3, 2015
Messages
1
Thanks for the code that you sent in here. That works for me too. I just want to go one step ahead and display the column name for that maximum field. Can you please show me how to do that?

Thank in Advance !!!
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:50
Joined
Jan 20, 2009
Messages
12,849
I just want to go one step ahead and display the column name for that maximum field.

Testing values in different fields against each other strongly suggests an inappropriate data structure. Wanting to know the name of the field with the maximum value confirms it.

These values should be stored in a related table with one record for each value. Another field stores a value to indicate which field the data would have been stored in using the current structure.

It is then a simple matter to find the maximum and display the name in the other field.
 

cigarprofiler

Registered User.
Local time
Today, 04:50
Joined
Mar 25, 2017
Messages
32
This is very handy. I applied it to a query on a crosstab query, which enabled me to find the mode of each row (the column containing the highest number).
 

jukos

New member
Local time
Today, 04:50
Joined
Dec 15, 2017
Messages
9
This solution worked beautifully for me. Thank you!
 

Tezza444

New member
Local time
Today, 04:50
Joined
Dec 13, 2019
Messages
7
Hi

I want to do the same but when I run the query I get undefined function Maximum.
Have been using Access for a while but very new to coding.

Tezza444
 

jukos

New member
Local time
Today, 04:50
Joined
Dec 15, 2017
Messages
9
You need to add the "Maximum" function in a module.

Function Maximum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim i As Integer
Dim currentVal As Variant

' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)

' Cycle through each value from the row to find the largest.

For i = 0 To UBound(FieldArray)
If FieldArray(i) > currentVal Then
currentVal = FieldArray(i)
End If
Next i

' Return the maximum value found.
Maximum = currentVal

End Function
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:50
Joined
Feb 28, 2001
Messages
26,996
At different times during this thread that is now pushing 10 years old, both Galaxiom and I have commented on a simple fact. IF it is meaningful for you to compare fields in the same record in this way, you have a data structure that is not normalized.

Excel can surely do this, but Access is designed differently which is why you have to go to the trouble of creating a function for it. This kind of activity is NOT NATIVE to Access. As such, you can expect it to be more difficult.
 

Tezza444

New member
Local time
Today, 04:50
Joined
Dec 13, 2019
Messages
7
Thanks Jukos

I have done that. see below


Function Maximum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant

' Set the variable currentVal equal to the array of values.
currentVal = FieldArray()

' Cycle through each value from the row to find the largest.

For I = 0 To UBound(FieldArray)
If FieldArray(I) > currentVal Then
currentVal = FieldArray(I)
End If
Next I

' Return the maximum value found.
Maximum = currentVal

End Function

Is copied into the module and the query created has the following expression in

T1Max: Maximum([T1],[T2],[T3],[T4],[T5],[T6],[T7],[T8],[T9],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20])

I have probably made a rookie mistake, but I am a rookie!

Tezza444
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:50
Joined
Jan 20, 2009
Messages
12,849
I have probably made a rookie mistake, but I am a rookie!

Yes. You have continued to develop your database with an inappropriate data structure that belongs in a spreadsheet rather than a database.

You will continue to require clumsy, inefficient workarounds until you understand database normalization.
 

Tezza444

New member
Local time
Today, 04:50
Joined
Dec 13, 2019
Messages
7
Thanks for the advice. I am new to this and though I appreciate your comment it did zero to help
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:50
Joined
Sep 21, 2011
Messages
14,038
If you have to stick your head in the sand then try

Code:
currentVal = FieldArray(0)

For I = 1 To UBound(FieldArray)
    If FieldArray(I) > currentVal Then
        currentVal = FieldArray(I)
    End If
Next I
 

Tezza444

New member
Local time
Today, 04:50
Joined
Dec 13, 2019
Messages
7
Thanks Gasman. Again another comment unexplained.
I am not sticking my head in the sand I am learning.
I can play the saxophone and knit an arran sweater with ease but I am new to database code.
If you are going to make comments explain what you mean or don't bother commenting!!!!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:50
Joined
Jul 9, 2003
Messages
16,244
Thanks for the advice. I am new to this and though I appreciate your comment it did zero to help
I disagree with your observation that you got "zero help". What you actually got was advice that you don't like, hence your dissatisfaction with it.

You're not the first and you will not be the last person that comes here with a database constructed incorrectly, and then ignore the advice to look at the structure and fix it.

That's good advice you are ignoring!

I spell it out on my website here:-


https://www.niftyaccess.com/excel-in-access/



Sent from Newbury UK
 

Tezza444

New member
Local time
Today, 04:50
Joined
Dec 13, 2019
Messages
7
Yes. You have continued to develop your database with an inappropriate data structure that belongs in a spreadsheet rather than a database.

You will continue to require clumsy, inefficient workarounds until you understand database normalization.

Explain what you mean instead of trying to sound clever!!!!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:50
Joined
Jul 9, 2003
Messages
16,244
If you are going to make comments explain what you mean or don't bother commenting!!!!

Oh! A bitch! I like taking down bitches.

I just hope you don't lose your Balls and flounce away.

Sent from Newbury UK
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom