Restrict Truncation

[mubi_masti]

I am concatenating different fields by using following text. two fields are text while one is memo field. The function is working well when i use "unique records" ON but by doing this output results truncated by access.

if i remove unique records then query provide whole text but it produce error

how i can avoid truncatination of memo field.

Function Concatenate(pstrSQL As String, Optional pstrDelim As String = ", ", Optional pstrLastDelim As String = "") As Variant
' Created by Duane Hookom, 2003
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)
Dim intLenB4Last As Integer
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
intLenB4Last = Len(strConcat)
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len(pstrDelim))
If Len(pstrLastDelim) > 0 Then
strConcat = Left(strConcat, intLenB4Last - Len(pstrDelim) - 1) & pstrLastDelim & Mid(strConcat, intLenB4Last + 1)
End If
End If
If Len(strConcat) > 0 Then
Concatenate = strConcat
Else
Concatenate = Null
End If
End Function

 

[spikepl]

http://allenbrowne.com/ser-63.html

 

[mdlueck]

Additionally I bumped into a limitation of Text Box controls and trying to display an entire SQL Server VarChar(MAX) in them. (Well not even Meg's worth of VarChar... a couple of hundred thousand characters would crash Access!)

According to this page:
http://office.microsoft.com/en-us/access-help/access-2007-specifications-HA010030739.aspx

"Number of characters in a text box: 65,535"

So that would be why Access crashes in my scenario.