Need quick fix in this concatenation query for access

I'm using this code provided my another user on experts-exchange.
I awarded points seeing that it works however it was not entirely working just yet, my mistake.

It is concatenating the attributes fiemd however it is concatenating all of them over and over.

What I have is a Prepare_Attributes Table:
Product_Number           Atribute
CAT1234                      FDD1234
CAT1234                      GBB1234
DOG1234                      BBB3333
DOG1234                      CCC4444

I need the data like this:
CAT1234               FDD1234|GBB1234
DOG1234              BBB3333|CCC4444

But its currently coming out like this:
CAT1234        BBB3333|CCC4444|FDD1234|GBB1234
DOG1234       BBB3333|CCC4444|FDD1234|GBB1234

So the code is concatenating but it's concating incorrectly.

Thanks in advance for the help.
P.S.  I'd prefer to continue using this concat function as I like the way it allows me to use it in different types of queries,  i think I just need help on the SQL Select part.
Public Function fConcatenateRecords(strField As String, strRecordset As String, strFieldSeparator As String) As String
'USAGE:
'       fContatenateRecords(FieldName to concatenate, Table; Query; or SQL SELECT recordset, Separator character)
'NOTE:
'       DAO recordset is being used to provide backward compatability with Access 97
'       Make sure you enable the DAO reference
   On Error Resume Next 'To prevent query from hanging no error trapping involved
                        'If no records are return, you should look for the problem with your SQL SELECT statement
 
   Dim curDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim strTemp As String
 
   Set curDB = CurrentDb
   Set rst = curDB.OpenRecordset(strRecordset)
       With rst
           If .EOF And .BOF Then
           fConcatenateRecords = "" 'no records returned
           Exit Function
       End If
 
       .MoveFirst
       While Not .EOF
           strTemp = strTemp & .Fields(strField) & strFieldSeparator & " "
           .MoveNext
       Wend
       .Close
   End With
 
   strTemp = Left(strTemp, Len(strTemp) - (Len(strFieldSeparator) + 1))
   fConcatenateRecords = strTemp
 
End Function

Open in new window

ryanvmcgeeAsked:
Who is Participating?
 
peter57rCommented:
I don't know what you were told about usingthis functioon, butv it seems to me that you have to supply it with a fully operational Select query of the forma..

"Select fieldname from table where id = somevalue"

It looks to me that you are only supplying :
"Select fieldname from table"
0
 
dan_nealCommented:
You will need a way to identify your key and concatenate until the key changes then set and clear the variable for the next key.  Will post code samle shortly.
0
 
dan_nealCommented:
Quick question.  How are you calling this function?  From a form/report or a query?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ryanvmcgeeAuthor Commented:
Calling from a query using the following
fContatenateRecords(Attributes,Prepare_Attributes, "|")
 
 
Original:
fContatenateRecords(FieldName to concatenate, Table; Query; or SQL SELECT recordset, Separator character)

Open in new window

0
 
mbizupCommented:
Pete is correct about needing a full SQL statement as an argument to the function.  To get the correct records, you need to supply criteria in a WHERE clause:

Assuming you want to call this from a query, use the function like this:

SELECT DISTINCT Product_Number, fConcatenateRecords("Attribute", "SELECT * FROM Prepare_Attributes WHERE Product_Number = '" & Product_Number & "'")
0
 
mbizupCommented:
Correction to my last post... You also need to include the | seperator as the last argument:

SELECT DISTINCT Product_Number, fConcatenateRecords("Attribute", "SELECT * FROM Prepare_Attributes WHERE Product_Number = '" & Product_Number & "'", "|")
0
 
dan_nealCommented:
Try the following function (updated to send key for grouping).
You should be able to call the function as
fContatenateRecords(Attributes,Prepare_Attributes,"|",Prepare_Attributes.Product_Number)

Public Function fConcatenateRecords(strField As String, strRecordset As String, strFieldSeparator As String, strParent as String) As String
'USAGE:
'       fContatenateRecords(FieldName to concatenate, Table; Query; or SQL SELECT recordset, Separator character)
'NOTE:
'       DAO recordset is being used to provide backward compatability with Access 97
'       Make sure you enable the DAO reference
   On Error Resume Next 'To prevent query from hanging no error trapping involved
                        'If no records are return, you should look for the problem with your SQL SELECT statement
 
   Dim curDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim strTemp As String
 
   Set curDB = CurrentDb
   Set rst = curDB.OpenRecordset(strRecordset)
       With rst
           If .EOF And .BOF Then
           fConcatenateRecords = "" 'no records returned
           Exit Function
       End If
 
       .MoveFirst
       While Not .EOF
			if .Fields(Product_Number) = strParent then
           strTemp = strTemp & .Fields(strField) & strFieldSeparator & " "
			end if
           .MoveNext
       Wend
       .Close
   End With
 
   strTemp = Left(strTemp, Len(strTemp) - (Len(strFieldSeparator) + 1))
   fConcatenateRecords = strTemp
 
End Function

Open in new window

0
 
ryanvmcgeeAuthor Commented:
Thanks for the advice everyone!     It works lperfect now,  Pete was correct about the full SQL Statement and mbizup your SQL statement worked great!  I'm going to split the points evenly.

Thanks again guys!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.