UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Display Listbox Column Min & Max In Textboxes On Form, Access 2016    
 
   
Jpalmer
post Apr 6 2020, 12:56 PM
Post#1



Posts: 9
Joined: 16-April 19



Hello Everyone,
Hope everyone is doing well!
I have the following code working exactly as I want. It will display the Maximum value of the listbox that is on my form on load, and will even display the Maximum value after I filter the listbox. However, I haven't been able to figure out how to make it work for the Minimum value of the same listbox column. As always, any insight that can be provided is greatly appreciated.

Public Function CalculateMaxPoints() As String
Const PointsCol = 34 '' column numbers start with 0
Const MaxCol = 34 '' second column has column index of 1
Const CompareNumeric = True '' convert strings to numbers for finding maximum

Dim RowIdx As Long
Dim MaxItem As Variant
Dim MaxIdx As Long
Dim CurrItem As Variant
Dim NewMaxFound As Boolean

MaxIdx = -1
MaxItem = Null
For RowIdx = 0 To Me.lstProspectManager.listCount - 1
CurrItem = Me.lstProspectManager.Column(MaxCol, RowIdx)
If CompareNumeric Then
CurrItem = Val(CurrItem)
End If

If IsNull(MaxItem) Then
NewMaxFound = True '' first one
Else
NewMaxFound = (CurrItem > MaxItem)
End If
If NewMaxFound Then
MaxItem = CurrItem
MaxIdx = RowIdx
End If
Next
If MaxIdx >= 0 Then
Me.txtMaxPoints.value = Me.lstProspectManager.Column(PointsCol, MaxIdx)

End If
End Function


Go to the top of the page
 
June7
post Apr 6 2020, 02:05 PM
Post#2



Posts: 1,413
Joined: 25-January 16
From: The Great Land


In future, please post lengthy code between CODE tags to retain indentation and readability.

What if multiple records have max or min value?

If you want to provide db for analysis, follow instructions at bottom of my post.

This post has been edited by June7: Apr 6 2020, 02:14 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
ADezii
post Apr 6 2020, 02:08 PM
Post#3



Posts: 2,994
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I am a little confused as to the nature of your Request. It appears to me that you wish to return the MINIMUM or MAXIMUM Value of a specific Column in a Listbox. If this is the case, then you can create a Function that will accept 3 Arguments, namely:
    1. A reference to the Listbox (see Graphic for Demo Listbox named lstDemo).
    2. The specific Column to return either the MIN or MAX Value within that Column.
    3. A Boolean Value that indicates whether you want the MAX Value in the Column (True) or the MIN Value (False).
  2. Function Definition:
    CODE
    Public Function fMinMaxOfColumn(lst As Access.ListBox, intColumn As Integer, _
                                    blnMax As Boolean) As Long
    Dim RowIdx As Integer
    Dim lngMax As Long
    Dim lngMin As Long

    ReDim arrNums(lst.ListCount - 1)

    For RowIdx = 0 To lst.ListCount - 1
      If RowIdx = 0 Then
        lngMax = lst.Column(intColumn, RowIdx)
        lngMin = lngMax
      End If
      If blnMax Then
        If lst.Column(intColumn, RowIdx) > lngMax Then lngMax = lst.Column(intColumn, RowIdx)
      Else
        If lst.Column(intColumn, RowIdx) < lngMin Then lngMin = lst.Column(intColumn, RowIdx)
      End If
    Next

    fMinMaxOfColumn = IIf(blnMax, lngMax, lngMin)
    End Function
  3. Sample Function Calls:
    CODE
    Debug.Print "Maximum Value in Column 4: " & fMinMaxOfColumn(Me![lstDemo], 3, True)
    Debug.Print "Minimum Value in Column 2: " & fMinMaxOfColumn(Me![lstDemo], 1, False)
    Debug.Print "Maximum Value in Column 3: " & fMinMaxOfColumn(Me![lstDemo], 2, True)
    Debug.Print "Minimum Value in Column 1: " & fMinMaxOfColumn(Me![lstDemo], 0, False)
  4. OUTPUT:
    CODE
    Maximum Value in Column 4: 110
    Minimum Value in Column 2: 9
    Maximum Value in Column 3: 153
    Minimum Value in Column 1: 30

P.S. - There has to be an easier way as projecttoday eluded to, but it just escapes me at the moment.
This post has been edited by ADezii: Apr 6 2020, 02:36 PM
Attached File(s)
Attached File  Listbox.JPG ( 33.09K )Number of downloads: 1
 
Go to the top of the page
 
projecttoday
post Apr 6 2020, 02:12 PM
Post#4


UtterAccess VIP
Posts: 12,205
Joined: 10-February 04
From: South Charleston, WV


=Max(...) and = Min(...) in the form footer?

--------------------
Robert Crouser
Go to the top of the page
 
Jpalmer
post Apr 6 2020, 03:34 PM
Post#5



Posts: 9
Joined: 16-April 19



ADezii,
Thank you for your reply and sample code.
QUOTE
I am a little confused as to the nature of your Request. It appears to me that you wish to return the MINIMUM or MAXIMUM Value of a specific Column in a Listbox.


Hopefully, I can explain more clearly what it is I am trying to accomplish.
1. I have a form with a listbox, combobox, and two textboxes. One textbox is for displaying the Min value of the listbox column(Points), the second textbox is for displaying the Max value of the same column. The combobox selects a specific customer, and then with the On Click event of a button, filters the listbox to show only those records that match the combobox selection.

If I put the following code in the On Load event of the form it returns the Min value & Max value of all records....exactly what I want. (Ex. MinPoints = 30 - MaxPoints = 130.)
The problem is when I filter the listbox it doesn't change the Min & Max range for that specific customer (Ex. Customer "ABC" MinPoints =55 - MaxPoints = 95.)

CODE
Me.txtMinPoints = DMin("[Points]", "QryProspectManager")
    'Me.txtMaxPoints = DMax("[Points]", "QryProspectManager")


The code I originally shared will correctly display the MaxPoints value with the On Load Event and after I filter the listbox. Works perfectly! The problem I am having is getting that code to work with the MinPoints value.
Would your code do what I looking to do?

Go to the top of the page
 
ADezii
post Apr 6 2020, 03:54 PM
Post#6



Posts: 2,994
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
Would your code do what I looking to do?

Immediately after Filtering the Listbox, in the Click() Event of the Command Button, you can call the Function then write the Return Value to a Listbox.
As an example, the following Code will write the MINIMUM Value in the 2nd Column of lstDemo to the txtMinPoint Textbox:
CODE
Me![txtMinPoint] = fMinMaxOfColumn(Me![lstDemo], 1, False)

This post has been edited by ADezii: Apr 6 2020, 03:57 PM
Go to the top of the page
 
tina t
post Apr 6 2020, 03:58 PM
Post#7



Posts: 6,601
Joined: 11-November 10
From: SoCal, USA


QUOTE
The combobox selects a specific customer, and then with the On Click event of a button, filters the listbox to show only those records that match the combobox selection.

okay. does a selection in the combobox also filter the RecordSource of the form, or only the RowSource of the listbox?

and, can you post the SQL statement of the listbox control's RowSource? and post the code that runs when you click the button to filter the listbox?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Jpalmer
post Apr 6 2020, 04:33 PM
Post#8



Posts: 9
Joined: 16-April 19



Tina,
QUOTE

- Only the RowSource Of the listbox
CODE
SELECT ProspectData.PSPID, ProspectData.AccountID, ProspectData.PrincipalID, ProspectData.Principal, ProspectData.DateCreated, ProspectData.Company, ProspectData.City, ProspectData.State, ProspectData.ZipCode, ProspectData.Region, ProspectData.Country, ProspectData.SiteAnnualRevenue, ProspectData.CompanyOwnership, ProspectData.LeadSource, ProspectData.Category, ProspectData.QualificationStage, ProspectData.SalesFunnelDecisionCyclePhase, ProspectData.BuyingMode, ProspectData.TAM, ProspectData.MarketSegment, ProspectData.SICCodeIndustryGroup, ProspectData.ProductServiceNeeds, ProspectData.MinimumRequiredQualityCertifications, ProspectData.SalesPerson, ProspectData.HaveaCoach, ProspectData.HeldFacetoFaceMeeting, ProspectData.HostFacilityAuditDecisionTeamVisit, ProspectData.CreditAnalysis, ProspectData.PrincipalonAVL, ProspectData.SignedNDA, ProspectData.StrenghtsDifferentiators, ProspectData.RedFlagsWeaknesses, ProspectData.AccountStatus, ProspectData.NextAction, ProspectData.Points
FROM ProspectData
ORDER BY ProspectData.Company;

CODE
Private Sub cmdSearchByCompanyName_Click()
    Dim SQL As String
        
        SQL = "SELECT ProspectData.PSPID, ProspectData.AccountID, ProspectData.PrincipalID, ProspectData.Principal, ProspectData.DateCreated, ProspectData.Company, ProspectData.City, ProspectData.State, ProspectData.ZipCode, ProspectData.Region, ProspectData.Country, ProspectData.SiteAnnualRevenue, ProspectData.CompanyOwnership, ProspectData.LeadSource, ProspectData.Category, ProspectData.QualificationStage, ProspectData.SalesFunnelDecisionCyclePhase, ProspectData.BuyingMode, ProspectData.TAM, ProspectData.MarketSegment, ProspectData.SICCodeIndustryGroup, ProspectData.ProductServiceNeeds, ProspectData.MinimumRequiredQualityCertifications, ProspectData.SalesPerson, ProspectData.HaveaCoach, ProspectData.HeldFacetoFaceMeeting, ProspectData.HostFacilityAuditDecisionTeamVisit, ProspectData.CreditAnalysis, ProspectData.PrincipalonAVL, ProspectData.SignedNDA, ProspectData.StrenghtsDifferentiators, ProspectData.RedFlagsWeaknesses, ProspectData.AccountStatus, ProspectData.NextAction, ProspectData.Points " _
            & "From ProspectData " _
            & "WHERE (((ProspectData.SalesPerson) = [Forms]![frmLogin]![txtUserLogin])) "

        If Me.txtCompany.value & "" <> "" Then
            SQL = SQL & " AND Company LIKE '" & Me.txtCompany & "*' "
        End If
        
        If cboPrincipal.Column(1) & "" <> "" Then
            SQL = SQL & " AND ProspectData.Principal = '" & cboPrincipal.Column(1) & "' "
        End If
        
        SQL = SQL & " ORDER BY ProspectData.Company"
        
    Forms!frmProspectManager.lstProspectManager.RowSource = SQL
    Forms!frmProspectManager.lstProspectManager.Requery
    Forms.frmProspectManager.CalculateMaxPoints
Go to the top of the page
 
ADezii
post Apr 6 2020, 05:19 PM
Post#9



Posts: 2,994
Joined: 4-February 07
From: USA, Florida, Delray Beach


CODE
'**************** CODE INTENTIONALLY OMITTED ****************
Dim lst As Access.ListBox

Set lst = Forms!frmProspectManager.lstProspectManager

lst.RowSource = SQL
lst.Requery
Me![txtMinPoint] = fMinMaxOfColumn(lst, 34, False)
'***********************************************************

Just had another brainstorm. Since the Filter for the RowSource is already defined and is operational, why not use strSQL, create a Recordset based on it, sort the Recordset by [Points] ASC? The first Record in this Recordset will be the MINIMUM Value in the Filtered RowSource. I did test the Code and it works as intended, only you can determine whether it is useful or not. Some Code has been omitted!
CODE
Dim rst As DAO.Recordset
Dim rstSorted As DAO.Recordset
Dim lst As Access.ListBox

Set lst = Forms!frmProspectManager.lstProspectManager

lst.RowSource = SQL
lst.Requery

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)      'strSQL previously defined
  rst.Sort = "Points"
Set rstSorted = rst.OpenRecordset       'Sorted by [Points]

If Not rstSorted.BOF And Not rstSorted.EOF Then     'Not Empty, contains Record(s)
  rstSorted.MoveFirst            'Will be the MINIMUM Point
    MsgBox "Minimum Point For Filter: " & rstSorted![Points]
End If

rst.Close
rstSorted.Close
Set rst = Nothing
Set rstSorted = Nothing

This post has been edited by ADezii: Apr 6 2020, 06:10 PM
Go to the top of the page
 
tina t
post Apr 6 2020, 11:18 PM
Post#10



Posts: 6,601
Joined: 11-November 10
From: SoCal, USA


well, if you filtered the form's RecordSource to match the filter applied to the listbox's RowSource, then, as Robert suggested, getting the Min and Max values from a particular field (or fields) would be easy. just use Min() and Max() expressions in unbound textbox controls' ControlSource properties.

if you can't or don't want to do that, then...well, i'm not a SQL whiz, so i'd probably use the SQL statement you posted as a hard-coded query object, with the appropriate form/control references, but including only the Points field in the query's output - at least, as near as i can tell, that's the field you're targeting for your Min/Max display. then i'd probably put two unbound textbox controls on the form to pull the min and max values, as

=DMax("Points","MyQueryName")

=DMin("Points","MySameQueryName")

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
projecttoday
post Apr 7 2020, 04:50 AM
Post#11


UtterAccess VIP
Posts: 12,205
Joined: 10-February 04
From: South Charleston, WV


Max and Min are a suggestion. I don't think there are Max and Min for listboxes. So you would have to change the listbox to a form. Dmax and Dmin read tables. They will not give you the correct result unless there is no possibility of anyone making more entries into the table but still a bad idea because that could change in the future.

--------------------
Robert Crouser
Go to the top of the page
 
Jpalmer
post Apr 7 2020, 01:40 PM
Post#12



Posts: 9
Joined: 16-April 19



Thanks to everyone's extremely valuable insight & input I would not been able to finally get this to work! woohoo.gif

The following Public Function Code finally achieved what I was looking to do. Full disclosure, the code is a manipulated version of code that Gord Thompson had posted to Stacked Overflow 7 years ago.
CODE
Public Function CalculateMinMaxPoints() As String
    Const PointsCol = 34  '' column numbers start with 0
    Const MinCol = 34  '' second column has column index of 1
    Const MaxCol = 34
    Const CompareNumeric = True  '' convert strings to numbers for finding maximum

    Dim RowIdx As Long
    Dim MinItem As Variant
    Dim MinIdx As Long
    Dim MaxItem As Variant
    Dim MaxIdx As Long
    Dim CurrItem As Variant
    Dim NewMinFound As Boolean
    Dim NewMaxFound As Boolean

    MinIdx = -1
    MinItem = Null

    MaxIdx = -1
    MaxItem = Null

    For RowIdx = 0 To Me.lstProspectManager.listCount - 1
        CurrItem = Me.lstProspectManager.Column(MinCol, RowIdx)
    
    If CompareNumeric Then
        CurrItem = Val(CurrItem)
    End If
    
    If IsNull(MinItem) Then
        NewMinFound = True
    Else
        NewMinFound = (CurrItem < MaxItem)
    End If
    
    If NewMinFound Then
        MinItem = CurrItem
        MinIdx = RowIdx
    End If
    
    If MinIdx >= 0 Then
        Me.txtMinPoints.value = Me.lstProspectManager.Column(PointsCol, MinIdx)
    End If
    
    If IsNull(MaxItem) Then
        NewMaxFound = True  '' first one
    Else
        NewMaxFound = (CurrItem > MaxItem)
    End If
    
    If NewMaxFound Then
        MaxItem = CurrItem
        MaxIdx = RowIdx
    End If
Next
    If MinIdx >= 0 Then
        Me.txtMinPoints.value = Me.lstProspectManager.Column(PointsCol, MinIdx)
    End If
    
    If MaxIdx >= 0 Then
        Me.txtMaxPoints.value = Me.lstProspectManager.Column(PointsCol, MaxIdx)
    End If
End Function


The following attachment illustrates the "On Load Event" of the Form "frmProspectManager". If you'll notice the two textboxes (txtMinPoints & txtMaxPoints) that are highlighted (yellow), they are displaying the correct points Range for the Unfiltered form.
Since the form is Unfiltered on load, I used projecttoday's suggestion.

CODE
  Me.txtMinPoints = DMin("[Points]", "QryProspectManager")
        Me.txtMaxPoints = DMax("[Points]", "QryProspectManager")

Attached File  Prospect_Manager_On_Open___No_Filters_Applied.png ( 135.86K )Number of downloads: 2


The following attachment illustrates the same form "Filtered" by using the after Update Event of the Principal Combobox.
CODE
Dim SQL As String
        
        SQL = "SELECT ProspectData.PSPID, ProspectData.AccountID, ProspectData.PrincipalID, ProspectData.Principal, ProspectData.DateCreated, ProspectData.Company, ProspectData.City, ProspectData.State, ProspectData.ZipCode, ProspectData.Region, ProspectData.Country, ProspectData.SiteAnnualRevenue, ProspectData.CompanyOwnership, ProspectData.LeadSource, ProspectData.Category, ProspectData.QualificationStage, ProspectData.SalesFunnelDecisionCyclePhase, ProspectData.BuyingMode, ProspectData.TAM, ProspectData.MarketSegment, ProspectData.SICCodeIndustryGroup, ProspectData.ProductServiceNeeds, ProspectData.MinimumRequiredQualityCertifications, ProspectData.SalesPerson, ProspectData.HaveaCoach, ProspectData.HeldFacetoFaceMeeting, ProspectData.HostFacilityAuditDecisionTeamVisit, ProspectData.CreditAnalysis, ProspectData.PrincipalonAVL, ProspectData.SignedNDA, ProspectData.StrenghtsDifferentiators, ProspectData.RedFlagsWeaknesses, ProspectData.AccountStatus, ProspectData.NextAction, ProspectData.Points " _
            & "From ProspectData " _
            & "WHERE (((ProspectData.SalesPerson) = [Forms]![frmLogin]![txtUserLogin])) "
  
        If cboPrincipal.Column(1) & "" <> "" Then
            SQL = SQL & " AND ProspectData.Principal = '" & cboPrincipal.Column(1) & "' "
        End If
        
        SQL = SQL & " ORDER BY ProspectData.Points DESC"
        
    Forms!frmProspectManager.lstProspectManager.RowSource = SQL
    Forms!frmProspectManager.lstProspectManager.Requery
   Forms.frmProspectManager.CalculateMinMaxPoints


Attached File  Prospect_Manager_Filtered.png ( 65.56K )Number of downloads: 2
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    30th May 2020 - 10:36 AM