Concatenate function limited to 255 chars?

[Keir]

I want to combine multiple records deliminated by a comma
into one field. I am using the concatenate function by
Jerry Dennison below. It works, but only for first 250-330
characters. The total number of characters in combined
records is probably about 5000. How do I overcome this?


FUNCTION:

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

 

[Duane Hookom]

Since this is a query NG, I assume the results of your function are
displayed in a query. You probably have a word(s) like "DISTINCT" or "GROUP
BY". Try remove these. There may be other options but we can't see your SQL.