Concat

[Drather]

Hello,
I am looking for some help on how to edit the following code to remove the 255 character restriction. The more I look into why there is a 255 char limit the more I feel I am missing.

Thanks in advance. (for those interested, I will try to post a follow up with more info on what the code is used for)

Public Function ConcatRelated(strField As String, _
    strTable As String, _
    Optional strWhere As String, _
    Optional strOrderBy As String, _
    Optional strSeparator = " ") As Variant
On Error GoTo Err_Handler
    'Purpose:   Generate a concatenated string of related records.
    'Return:    String variant, or Null if no matches.
    'Arguments: strField = name of field to get results from and concatenate.
    '           strTable = name of a table or query.
    '           strWhere = WHERE clause to choose the right values.
    '           strOrderBy = ORDER BY clause, for sorting the values.
    '           strSeparator = characters to use between the concatenated values.
    'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
    '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
    '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
    '           4. Returning more than 255 characters to a recordset triggers this Access bug:
    '               http: // allenbrowne . com / bug-16.ht ml
    Dim rs As DAO.Recordset         'Related records
    Dim rsMV As DAO.Recordset       'Multi-valued field recordset
    Dim strSql As String            'SQL statement
    Dim strOut As String            'Output string to concatenate to.
    Dim lngLen As Long              'Length of string.
    Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
    
    'Initialize to Null
    ConcatRelated = Null
    
    'Build SQL string, and get the records.
    strSql = "SELECT " & strField & " FROM " & strTable
    If strWhere <> vbNullString Then
            strSql = strSql & " WHERE " & strWhere
    End If
    If strOrderBy <> vbNullString Then
        strSql = strSql & " ORDER BY " & strOrderBy
    End If
    Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
    'Determine if the requested field is multi-valued (Type is above 100.)
    bIsMultiValue = (rs(0).Type > 100)
    
    'Loop through the matching records
    Do While Not rs.EOF
        If bIsMultiValue Then
            'For multi-valued field, loop through the values
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
                If Not IsNull(rsMV(0)) Then
                    strOut = strOut & rsMV(0) & strSeparator
                End If
                rsMV.MoveNext
            Loop
            Set rsMV = Nothing
        ElseIf Not IsNull(rs(0)) Then
            strOut = strOut & rs(0) & strSeparator
        End If
        rs.MoveNext
    Loop
    rs.Close
    
    'Return the string without the trailing separator.
    lngLen = Len(strOut) - Len(strSeparator)
    If lngLen > 0 Then
        ConcatRelated = Left(strOut, lngLen)
    End If

Exit_Handler:
    'Clean up
    Set rsMV = Nothing
    Set rs = Nothing
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
    Resume Exit_Handler
End Function

 

[Drather]

Some additional info.
I am currently using office 2013 (excel and access primarily).

I am, and find most people I deal with, are more familiar/comfortable using Excel. I have found in the past there are some things that Access is better at, so I am trying to learn how to use it more efficiently.

For this project, an excel table is exported from the project access database to be filled out with results. Six different tables are sent to 5 judges. Some judges may only get 1 table, others may get a few but in total, the six tables are received by at least 5 different people. The judges fill out the excel file and all submitted tables are combined into a single excel file and imported back into Access.

The numerical results and comments are new data, everything else is pre-populated by the export. The numerical results are averaged, and the comment field is the field I am trying to combine but some of the judges are fairly wordy in their responses.

The code I am using was something a coworker found a few years back for a different project. I understood enough of the process to adjust it to suit this project but the 255 limit is an issue this time (last time we only worked on 1-3 char lengths).

The concatenation is done through a series of create/update table queries.
1. Create table "CommentsCondense"
2. Replace char(34) with ~
3. Create table"CommentsCondense - Final" while run the VBA code on first table
4. Update by replace ~ with char(34) in "CommentsCondse"
5. Update by replace ~ with char(34) in "CommentsCondse - Final"

I am trying to combine the comments to a single line so they can be displayed together with the averaged results.

Thanks in advance

 

[The_Doc_Man]

To which 255 character limit were you referring?

A string actually can be longer than 255 bytes. It isn't about the string itself. It is where you store it or what you call that will do something with it.

 

[Drather]

That is the issue I am trying to understand The_doc_man. The more I look into it, I cannot see why there is a limit of 255 since it uses String; but when I ran it last time, I was losing any information over 255 char.

I originally hoped it was just a matter of redefining a variable from String type, but String does not appear to be the issue. I have no idea why there is an issue. The original creator did leave a comment about which bug was creating the limit, but I did not understand what they were talking about. (Note 4 in the comments at the top of the code). I could not post as an actual URL yet - so I added some spaces...