none
Truncated Memo Field using Allen Browne Concat Function RRS feed

  • Question

  • While reading my issue, please keep in mind that I have learned everything I know from Google, the Access Bible and trial and error.

    I am creating a database where I have a Main table, a Package table and a join table to bring the two together.

    The Main table contains the DocID (and other info. not relevant to this issue so I will leave it off). Each document gets a one to many PackageID.

    Once the tables were setup, I created a query using Allen Browne’s method to Concat the PackageID so they displayed in one field/cell per document.

    The problem lies in the fact that my query will display a DocID multiple times for however many packages the document has pre-Concat (Query C image below). If I use DISTINCT or GROUP BY, I hit the 255 character limit and my text will truncate. If I remove the DISTINCT or GROUP BY, then I do get all of the PackageID in the list but multiple DocIDs.

    I added the columns to the query using Access, then in SQL wrote the Concat piece per Allen Browne’s instructions.

    SQL code:

    SELECT DISTINCT Main.DocID, ConcatRelated("DocPackagejoin.PackageID","DocPackagejoin","DocPackagejoin.DocID = " & [DocPackagejoin].[DocID]) AS Packages FROM Main INNER JOIN DocPackagejoin ON Main.[DocID] = DocPackagejoin.[DocID];

    I want to take Query A, make it like Query B but I am getting Query C. If I use DISTINCT or GROUP then the PackageID cuts off after 255 characters.

    Query A

    DocID      PackageID

    1              A

    1              B

    1              C

    2              A

    3              B

    Query B

    DocID      PackageID

    1              A, B, C

    2              A

    3              B

    Query C

    DocID      PackageID

    1              A, B, C

    1              A, B, C

    1              A, B, C

    2              A

    3              B

    Thursday, December 14, 2017 4:40 PM

All replies

  • Hi SOD - Save My Database,

    I can see that you are already using the Allen Browne’s method

    but you had mentioned that," I hit the 255 character limit and my text will truncate."

    As per documentation, it is the limitation here.

    so I think that here you need to think about work around to solve this issue.

    you can try to refer link below. that may help you to solve your issue.

    you can try to understand the logic and try to modify it as per your requirement.

    Concatenate Column Values from Multiple Rows into a Single Column with Access

    Output:

    sample code:

    Public Function FixTable() As Boolean
    On Error Resume Next
    
    Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
    Dim strColumn1 As String, strColumn2 As String
    
    Set db = CurrentDb()
    Call RecreateTables(db)
    
    Stop
    sSQL = "SELECT Column1, Column2 FROM tblOriginal " _
           & "ORDER BY Column1, Column2 ASC"
    Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)
    
    If Not rst.BOF And Not rst.EOF Then
      rst.MoveFirst
      strColumn1 = rst!Column1
      strColumn2 = rst!Column2
      
      rst.MoveNext
      Do Until rst.EOF
        If strColumn1 = rst!Column1 Then
          strColumn2 = strColumn2 & ", " & rst!Column2
        Else
          sSQL = "INSERT INTO tblCopy (Column1, Column2)  " _
               & "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
          db.Execute sSQL
          strColumn1 = rst!Column1
          strColumn2 = rst!Column2
        End If
        rst.MoveNext
      Loop
      
      ' Insert Last Record
      sSQL = "INSERT INTO tblCopy (Column1, Column2)  " _
           & "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
      db.Execute sSQL
    End If
    
    Set rst = Nothing
    Set db = Nothing
    
    DoCmd.OpenForm "frmOutput"
    
    End Function
    

    you can download the sample database from link below.

    Download Sample Database

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, December 15, 2017 2:17 AM
    Moderator
  • Should I be able to copy/paste that sample code into a module in my database? If I do, I get an error when I try to run it in debugger.
    Monday, December 18, 2017 3:17 PM
  • Hi,

    Not sure if it's any better but I would try using this simple function with a slightly different SQL statement. For example:

    SELECT SQ.DocID As UnqDocID,
    SimpleCSV("SELECT PackageID FROM Main WHERE DocID=" & UnqDocID) As AllPackageID
    FROM (SELECT DISTINCT T1.DocID FROM Main T1) SQ

    (untested)

    Hope it helps...

    Monday, December 18, 2017 4:38 PM
  • Hi SOD - Save My Database,

    you had mentioned that,"I get an error when I try to run it in debugger."

    can you post your modified code.

    from the description only we are not able to find the issue.

    if you post the code then we will try to make a test with your code and try to correct the issue.

    also try to post the detailed error message that you got.

    it will be helpful to find the cause of the issue.

    please try to provide above mentioned information regarding your issue.

    we will try to provide further suggestions to solve your issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 19, 2017 1:09 AM
    Moderator
  • Hi SOD - Save My Database,

    is your issue solved?

    I find that you did not follow up this thread after my last post.

    if your issue is solved then I suggest you to post your solution and mark it as an answer.

    if your issue is still exist then try to refer the solution given by the me.

    if then also you have any further questions then let me know about it.

    I will try to provide further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 27, 2017 8:57 AM
    Moderator