Concatenate fields

[capsula4]

Hi! I want to concatenate information from a same field but different records.

So I took a sample I found and tried to make it more generic by adding more arguments, but it seems like not working.

Can you take a look at it and see if you find any error? When I execute the function in a query, it appears a popup: "Error 3265: any element was found on this collection" (translated from Spanish)

Option Compare Database
Option Explicit

Public Function fConcatenate(intID As Integer, strField As String, strTble As String)

Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "SELECT " & intID & ", " & strField
strSQL2 = "FROM " & strTble


Dim strSQL As String
Dim strText As String

strSQL = strSQL1 & " " & strSQL2


    'Open a Recordset and loop through it to fill the text box txtTest with
    'the faults Repaired >>>reprorted by the customer<<<<
Dim db As DAO.Database
Dim RS As DAO.Recordset
Set db = CurrentDb
Set RS = db.OpenRecordset(strSQL, dbOpenForwardOnly)
    
    Do Until RS.EOF
            If strText = "" Then 'This If statment prevents a  single comma at the begining of the text","
                strText = "" & RS!strField
                Else
                strText = strText & ", " & RS!strField
            End If
        RS.MoveNext
    Loop
    
    RS.Close
    Set RS = Nothing
    Set db = Nothing

Let fConcatenate = strText
End Function

It highlights strText = "" & RS!strField

 

[pbaldy]

Try

RS(strField)

 

[jdraw]

Hi! I want to concatenate information from a same field but different records.

So I took a sample I found and tried to make it more generic by adding more arguments, but it seems like not working.

Can you take a look at it and see if you find any error? When I execute the function in a query, it appears a popup: "Error 3265: any element was found on this collection" (translated from Spanish)

Option Compare Database
Option Explicit

Public Function fConcatenate(intID As Integer, strField As String, strTble As String)

Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "SELECT " & intID & ", " & strField
strSQL2 = "FROM " & strTble


Dim strSQL As String
Dim strText As String

strSQL = strSQL1 & " " & strSQL2


    'Open a Recordset and loop through it to fill the text box txtTest with
    'the faults Repaired >>>reprorted by the customer<<<<
Dim db As DAO.Database
Dim RS As DAO.Recordset
Set db = CurrentDb
Set RS = db.OpenRecordset(strSQL, dbOpenForwardOnly)
    
    Do Until RS.EOF
            If strText = "" Then 'This If statment prevents a  single comma at the begining of the text","
                strText = "" & RS!strField
                Else
                strText = strText & ", " & RS!strField
            End If
        RS.MoveNext
    Loop
    
    RS.Close
    Set RS = Nothing
    Set db = Nothing

Let fConcatenate = strText
End Function

It highlights strText = "" & RS!strField

Can you show how you call this function?
Where did you find the Let statement?
Suggest you review your original example before attempting to make it a general solution.

 

[capsula4]

This is how I call it and it worked with pbaldy suggestions!

CONT: fConcatenate([N_OP],"CONTENEDOR","CONTENEDORES")

Althought the original sample had a criteria on it and I removed it, so now I see all containers in each operation instead of the specific containers of each operation

(its a 1-many relationship, 1 transacion - many containers)

I will type the criteria thing again

Thank you jdraw for the suggestion, to tell you the truth I'm pretty newbie with Access and VBA but know a lil bit of C which helped me code this and I could make it work!

 

[capsula4]

Here is the function already finished, anybody can use it

fConcatenate("whereID",[criteriaID],"strField","strTble")

whereID: field being repeated in which you wanna group the different items. This mean this value should be between inverted commas "whereID"
criteriaID: the values of the field being repeated. Since we are speaking about values we don't have to put inverted commas, instead brackets.
strField: field with want to be concatenated (listed horizontally, separated by commas)
strTble: table/query from where we are gonna take the info above (strField, criteriaID, whereID).

Option Compare Database
Option Explicit

Public Function fConcatenate(whereID As String, criteriaID As Integer, strField As String, strTble As String)

Dim strSQL As String
Dim strText As String

strSQL = "SELECT " & whereID & ", " & strField & " FROM " & strTble & " WHERE (((" & whereID & ")=" & criteriaID & "))"


    'Open a Recordset and loop through it to fill the text box txtTest with
    'the faults Repaired >>>reprorted by the customer<<<<
Dim db As DAO.Database
Dim RS As DAO.Recordset
Set db = CurrentDb
Set RS = db.OpenRecordset(strSQL, dbOpenForwardOnly)
    
    Do Until RS.EOF
            If strText = "" Then 'This If statment prevents a  single comma at the begining of the text","
                strText = "" & RS(strField)
                Else
                strText = strText & ", " & RS(strField)
            End If
        RS.MoveNext
    Loop
    
    RS.Close
    Set RS = Nothing
    Set db = Nothing

Let fConcatenate = strText
End Function

 

[Uncle Gizmo]

Where did you find the Let statement?

Sorry the "Let" has propagated from the original code which I produced (no doubt from other people's examples) about four years ago when I was going through a phase of using "Let" because somebody said I shouldn't use it so I did. (it is my arkward nature) I have grown up a bit now and don't use it anymore, but that one slipped in unnoticed.

the original thread is here.

 

[cdoyle]

Hi,
This sounds like something that I'm trying to do, would you happen to have a sample db you could post?

 

[pbaldy]

I don't have a sample, but maybe this will help:

http://www.mvps.org/access/modules/mdl0004.htm