Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •