Display First/Last or Max/MIN

[Thompyt]

I have a form with an 2 unbound fields:

Me.MyChanges

Public Sub SelectChanges_Click()
    Dim StrWhere As String, ctl As Control, varItem As Variant, tbWhere As String, StrWhere2 As String
    
        Set ctl = Me.MyChanges
        
If Me.MyChanges.ItemsSelected.Count = 0 Then
     MsgBox "Nothing was selected"
        Exit Sub
    End If

For Each varItem In ctl.ItemsSelected
        StrWhere = StrWhere & "'" & ctl.ItemData(varItem) & "',"
        StrWhere2 = StrWhere2 & " " & ctl.ItemData(varItem) & ","


Next varItem
   StrWhere = Left(StrWhere, Len(StrWhere) - 1)
   StrWhere2 = Left(StrWhere2, Len(StrWhere2) - 2)
   Me.tbWhere = StrWhere2
    
   DoCmd.OpenReport "rptSelectChanges", acViewReport, , "CRNumber IN(" & StrWhere & ")"
End Sub

How do I go about only using the smallest and largest of the selected items?

.Subject = "Change(s) - " & First(MyChanges) & " - " & Last(MyChanges)

I think with the Format that it changes to a string vice a number?

SELECT Format((([tblChangeRequest].[CRNo])+([SubNo]*0.01)),"Fixed") AS  CRNumber FROM tblChangeRequest GROUP BY  Format((([tblChangeRequest].[CRNo])+([SubNo]*0.01)),"Fixed"),  tblChangeRequest.CRID HAVING (((tblChangeRequest.CRID)>25)) ORDER BY  tblChangeRequest.CRID;

Thanks
T

[June7]

Aggregate functions must be used in an aggregate (GROUP BY) query or in the ControlSource of a textbox. On a form the textbox would be in form header/footer section. On report could be in group/page/report header/footer section.

Domain aggregate functions (DLookup, DSum, DCount, etc.) can be used in query, ControlSource, or VBA.

Yes, Format function returns a string. Do formatting in textbox.

[Thompyt]

I figured how to get the first:
Sml = ctl.ItemData(varItem)

How would I get the last item from ctl?

[NTC]

I don't follow exactly what you seek. However my sense is that this should not be a code-centric approach. As J7 points out there are aggregate functions. The most straight forward way to implement these are with queries. Look for the sigma symbol (like a big E) - and there are instructions online as to how to work them for max, min, avg, sum, etc.

Make a query that results in the record correctly - and save it with a name.

Then in terms of your form - consider it only as a point of display (not a point of calculation). In your unbound textbox you bind it to a control source: =DLookUp("field name", "query name")

[Thompyt]

I am looking for the first and last item of ctl

so Me.MyChanges.ItemSelected Count = 1 should be the first item.

I am selecting them from Form_frmSelectChanges from an unbound field on frmSelectChanges.MyChanges which gets its information from:

SELECT Format((([tblChangeRequest].[CRNo])+([SubNo]*0.01)),"Fixed") AS CRNumber FROM tblChangeRequest GROUP BY Format((([tblChangeRequest].[CRNo])+([SubNo]*0.01)),"Fixed"), tblChangeRequest.CRID HAVING (((tblChangeRequest.CRID)>25)) ORDER BY tblChangeRequest.CRID;

What you are saying is that I should put a DMAX and DMIN in the above query?

I would like to do the coding route also:

If I have example data in CRNumber where CRID greater then 26:
1.00
1.01
1.02
1.03
2.00
2.01
3.00
3.01
3.02
3.03
In the form I select (Clicked on and highlighted) 1.00, 1.01, 1.02 and

If Me.MyChanges.ItemSelected Count = 1, then
ctl.ItemData(varItem) = 1.00
StrWhere2 = StrWhere2 & " " & ctl.ItemData(varItem) & "," would be 1.00

If Me.MyChanges.ItemSelected Count = 2, then
ctl.ItemData(varItem) = 1.01
StrWhere2 = StrWhere2 & " " & ctl.ItemData(varItem) & "," would be 1.00, 1.01

If Me.MyChanges.ItemSelected Count = 3, then
ctl.ItemData(varItem) = 1.02
StrWhere2 = StrWhere2 & " " & ctl.ItemData(varItem) & "," would be 1.00, 1.01, 1.02

Me.MyChanges.ItemSelected Count = 3 is the last/final ctl.ItemData(varItem) of this series.

All I want is the first VarItem and the last VarItem, so that I can put it in my .Subject line and part of my file naming convention with attachments.

[June7]

What is ctl? A multi-select extended listbox?

You want values from the first and last rows of extended listbox selection? Consider:

    Dim varItem As Variant
    Dim strFirst As String, strLast As String, strWhere As String
    Dim iCount As Integer
    iCount = 0
    With Me.myChanges
        If .ItemsSelected.Count <> 0 Then
            For Each varItem In .ItemsSelected
                strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
                If iCount = 0 Then strFirst = .ItemData(varItem)
                If iCount = .ItemsSelected.Count - 1 Then strLast = .ItemData(varItem)
                iCount = iCount + 1
            Next
            Me.tbWhere = Replace(strWhere, "'", "")
            DoCmd.OpenReport "rptSelectChanges", acViewReport, , "CRNumber IN(" & strWhere & ")"
        Else
            MsgBox "Nothing was selected from the list", vbInformation
        End If
    End With

...

.Subject = "Change(s) - " & strFirst & " - " & strLast

Or instead of the IN operator, use BETWEEN AND in the criteria. Eliminates the strWhere concatenation.

Me.tbWhere = strFirst & " - " & strLast
DoCmd.OpenReport "rptSelectChanges", acViewReport, , "CRNumber BETWEEN '" strFirst & "' AND '" & strLast & "'"

Review https://support.microsoft.com/en-us/...on-list-box-as

[Thompyt]

Thanks June7, what I was looking for. I did some tweaking and it does what I intended.