asked on

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

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dan_neal

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.

dan_neal

Quick question. How are you calling this function? From a form/report or a query?

ryanvmcgee

ASKER

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

mbizup

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 & "'")

SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dan_neal

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

ryanvmcgee

ASKER

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!!