Need help for Concat issue on access report

[lorel1975]

In a access report in got following group of records -

1 ball
2 bat
3 gloves
4 shoes

so there is two column. one colum "slno" another one "items".

solution 1 needed - I want to show all items of "items" column in a text box using a separator. Like this - ball/bat/gloves/shoes
solution 2 needed - i want to substract same data of another report, like this ball/bat

and end result should be - gloves/shoes.

Any help will be very much appreciated.

 

[arnelgp]

the first request will be easy, but how would you know which items to Exclude in request #2?

 

[Gasman]

For 1 try http://www.accessmvp.com/thedbguy/codes.php?title=simplecsv

 

[arnelgp]

Private Sub Report_Open(Cancel As Integer)

    Dim strText1 As String, strText2 As String
    Dim dict As Object
    Dim var
    Dim i As Integer
    Dim sExclude As String
    
    ' openargs will hold those Items to exclude
    ' in format: Item1/Item2[/Item(n)...]
    '
    If Not IsNull(Me.OpenArgs) Then
        sExclude = Replace$("/" & Me.OpenArgs & "/", "//", "/")
    End If
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    With CurrentDb.OpenRecordset("select Items from [YourTableName] group by Items;", dbOpenSnapshot, dbReadOnly)
        On Error Resume Next
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            dict(!Items & "") = 1
            .MoveNext
        Loop
        .Close
    End With
    
    For i = 0 To dict.Count - 1
        strText1 = strText1 & "/" & dict.keys()(i)
        If Len(sExclude) <> 0 Then
            If InStr(1, sExclude, "/" & dict.keys()(i) & "/") = 0 Then
                strText2 = strText2 & "/" & dict.keys()(i)
            End If
        Else
            strText2 = strText2 & "/" & dict.keys()(i)
        
        End If
    Next
    Me.UnboundTextbox1.ControlSource = "='" & Mid$(strText1, 2) & "'"
    Me.UnboundTextbox2.ControlSource = "='" & Mid$(strText2, 2) & "'"
        
    Set dict = Nothing
End Sub

 

[lorel1975]

the first request will be easy, but how would you know which items to Exclude in request #2?

That will come from another report and will be calculated exactly same way of no-1

 

[DHookom]

In a access report in got following group of records -

1 ball
2 bat
3 gloves
4 shoes

so there is two column. one colum "slno" another one "items".

solution 1 needed - I want to show all items of "items" column in a text box using a separator. Like this - ball/bat/gloves/shoes
solution 2 needed - i want to substract same data of another report, like this ball/bat

and end result should be - gloves/shoes.

Any help will be very much appreciated.

This generic function is my go to. Are the items always in alpha order and any subtractions be adjacent values?