# Thread: Max of Multiple Fields (A2k)

1. ## Max of Multiple Fields (A2k)

Hi everyone,

A friend of mine came to me with a scenario I've never come across before:

His database has a table with 12 different fields that store a numeric value. He's trying to find a way to report the field name and value of the field with the maximum quantity for each record.

For example:
Record A has 12 different fields named A, B, C, D, E, etc...
The value of A is 10, B is 25, C is 5, D is 13, etc.
With the limited data I've supplied the maximum is 25 in field B.

I can't seem to find a function to calculate the maximum across fields and report the field name

Any help?
Thanks!
Sam

2. ## Re: Max of Multiple Fields (A2k)

Does the record also contain other fields beside the twelve number fields whose maximum you want to determine, or are those the only fields in the record?

3. ## Re: Max of Multiple Fields (A2k)

Hans, Thanks for the quick reply!

There is only one other field. It's a text field describing a part.

Essentially, he needs to return:
Binford 2000, Maximum of 37 in field B
Binford 5000, Maximum of 23 in field G

etc.

Thanks again!

4. ## Re: Max of Multiple Fields (A2k)

Getting the maximum value is reasonably straightforward. Here is a VBA function (to be copied into a standard module) that can be used:
<code>
Public Function MaxVal(ParamArray FieldList() As Variant) As Variant
Dim i As Integer
MaxVal = Null
For i = 0 To UBound(FieldList)
If Not IsNull(FieldList(i)) And IsNull(MaxVal) Or (FieldList(i) > MaxVal) Then
MaxVal = FieldList(i)
End If
Next i
End Function
</code>
Use in the form
<code>
MaximumValue: MaxVal(<!t>[A]<!/t>,<!t>[B]<!/t>,<!t>[C]<!/t>,<!t>[D]<!/t>,<!t>[E]<!/t>,<!t>[F]<!/t>,<!t>[G]<!/t>,<!t>[H]<!/t>,<!t>[I]<!/t>,<!t>[J]<!/t>,<!t>[K]<!/t>,<!t>[L]<!/t>)
</code>
where A, B, C etc. are the field names.

To find the field having the maximum value is trickier. Here is a clunky version:
<code>
Public Function MaxField(ParamArray FieldList() As Variant) As Variant
Dim i As Integer
Dim iMax As Integer
Dim iCount As Integer
Dim MaxVal As Variant
iMax = -1
MaxVal = Null
iCount = (UBound(FieldList) + 1) 2
For i = 0 To iCount - 1
If Not IsNull(FieldList(i)) And IsNull(MaxVal) Or (FieldList(i) > MaxVal) Then
MaxVal = FieldList(i)
iMax = i
End If
Next i
If iMax > -1 Then
MaxField = FieldList(iMax + iCount)
End If
End Function
</code>
Use in the form
<code>
MaximumField: MaxField(<!t>[A]<!/t>,<!t>[B]<!/t>,<!t>[C]<!/t>,<!t>[D]<!/t>,<!t>[E]<!/t>,<!t>[F]<!/t>,<!t>[G]<!/t>,<!t>[H]<!/t>,<!t>[I]<!/t>,<!t>[J]<!/t>,<!t>[K]<!/t>,<!t>[L]<!/t>,"A","B',"C","D","E","F","G","H","I","J","K","L" )
</code>
An alternative would be to loop through the fields of a recordset.

5. ## Re: Max of Multiple Fields (A2k)

Hans,

This is perfect! I REALLY appreciate the time and help you gave me. It works exactly as needed. My friend is thrilled with the result and I learned some new code in the meantime. [img]/forums/images/smilies/smile.gif[/img]

Have a wonderful day!
Sam

6. ## Re: Max of Multiple Fields (A2k)

Hi Hans,
I've been trying to adapt your MaxVal function to my need which is getting the calendar in the startup form of the attached mdb to display the most recent day among the date(Giorno) field values of four tables in the database so in the Load event of the startup form I use

Dim a(3) As Date
a(0) = DMax("Giorno", "portdez23")
a(1) = DMax("Giorno", "g1dez")
a(2) = DMax("Giorno", "g2dez")
a(3) = DMax("Giorno", "g3dez")
Me.CGior = MaxVal(a())

but when the startup form opens I get the error message:
Invalid use of Null

7. ## Re: Max of Multiple Fields (A2k)

The code I posted has

Public Function MaxVal(ParamArray FieldList() As Variant) As Variant

A Variant can be set to Null. You changed it to

Public Function MaxVal(ParamArray FieldList()) As Date

A Date value cannot be set to Null.

8. ## Re: Max of Multiple Fields (A2k)

I've turned the MaxVal function back to its original form and now when the startup form opens I get the Run time error '13': Type mismatch and the code breaks at the line:
If Not IsNull(FieldList(i)) And IsNull(MaxVal) Or (FieldList(i) > MaxVal) Then

9. ## Re: Max of Multiple Fields (A2k)

You're using MaxVal incorrectly. In the Form_Load event procedure of Dezzo, call it like this:

Me.CGior = MaxVal(a(0), a(1), a(2), a(3))

Alternatively, you could rewrite MaxVal to have an array as argument instead of a ParamArray (= a list of arguments).

10. ## Re: Max of Multiple Fields (A2k)

Thank you Hans, I've gone for the option of having an array as argument instead of a ParamArray.

11. ## Re: Max of Multiple Fields (A2k)

One thing I don't understand is why, when I select either tab on the main form tab control, the Current event of the subform is not fired and so the various text boxes in the subform don't get set to the previous day's value.

12. ## Re: Max of Multiple Fields (A2k)

For some reason, the On Current box in the Properties window of each of the subforms is blank, although there is code for the On Current event. Select [Event Procedure] in the On Current box, then click the ... button to the right of it. Switch back to Access, then close and save the subform. Do this for each of the three.

BTW, the On Current event does not occur when you select a tab. It occurs when the main form is opened, and when you move to a different record.

13. ## Re: Max of Multiple Fields (A2k)

The problem now is that, on startup, I get the message:
Run-time error '3075':
Syntax error in the date in the expression of the query '[Giorno]<##'.
and the code breaks at the line:
lkup = DLookup("Lettur", "g1dez", "Giorno=#" & Format(DMax("giorno", "g1dez", _
"[Giorno]<#" & Format(Parent!CGior, "mm-dd-yyyy") & "#"), "mm-dd-yyyy") & "#")

because the Current event for the MG1Dez subform occurs before the main form loads and so the value of the calendar on the main form is not fetched.
How can I solve this?

14. ## Re: Max of Multiple Fields (A2k)

We're back to one of your old problems. I suggest you review previous threads, I'm sure we've discussed this before. It has nothing to do with the original subject of this thread.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•