[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

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

0
ryanvmcgee
Asked:
ryanvmcgee
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now