How do I avoid cutting off a memo field at 255 chars

[Guest]

I am using a recordset to retrieve data from an Access table.
The memo field in the table can hold more than 255 chars, but I
only retrieve the first 255. I have read about GetChunk and attempted
to fix the problem, but the same effect occurs. The code follows:

Dim DB As Database
Dim TB As Recordset
Dim ServiceNotes As String
Dim lngOffset As Long
Dim lngTotalSize As Long
Dim strChunk As String

Set DB = DBEngine(0)(0)
Set TB = DB.OpenRecordset("TSO Details", DB_OPEN_DYNASET)

On Error Resume Next
TB.MoveFirst
On Error GoTo 0

Do Until TB.EOF
Forms![TSO Details]![Service Resolution] = TB.Fields("Service
Resolution")

conChunkSize = 32768
lngTotalSize = TB.Fields("Service Notes").FieldSize
Do While lngOffset < lngTotalSize
strChunk = TB.Fields("Service Notes").GetChunk(lngOffset,
conChunkSize)
ServiceNotes = ServiceNotes & strChunk
lngOffset = lngOffset + conChunkSize
Loop

Forms![TSO Details]![Service Notes] = ServiceNotes


TB.MoveNext
Loop
Forms![TSO Details].Refresh

TB.Close
DB.Close

Please post any suggestions.

Thank you.

 

[Margaret Bartley]

It should work OK.
Have you stepped through the code to find out where the unexpected value
shows up?

 

[Guest]

I did step through the code, and the problem was that the data was
being updated properly (over 255 characters), but two tables were
linked for the data retrieval which seems to create a data limit.
I pulled directly from the table and the problem was resolved.
Does anyone know if this is the case for any join?

It should work OK.
Have you stepped through the code to find out where the unexpected value
shows up?

I am using a recordset to retrieve data from an Access table.
The memo field in the table can hold more than 255 chars, but I
only retrieve the first 255. I have read about GetChunk and attempted
to fix the problem, but the same effect occurs. The code follows:

Dim DB As Database
Dim TB As Recordset
Dim ServiceNotes As String
Dim lngOffset As Long
Dim lngTotalSize As Long
Dim strChunk As String

Set DB = DBEngine(0)(0)
Set TB = DB.OpenRecordset("TSO Details", DB_OPEN_DYNASET)

On Error Resume Next
TB.MoveFirst
On Error GoTo 0

Do Until TB.EOF
Forms![TSO Details]![Service Resolution] = TB.Fields("Service
Resolution")

conChunkSize = 32768
lngTotalSize = TB.Fields("Service Notes").FieldSize
Do While lngOffset < lngTotalSize
strChunk = TB.Fields("Service Notes").GetChunk(lngOffset,
conChunkSize)
ServiceNotes = ServiceNotes & strChunk
lngOffset = lngOffset + conChunkSize
Loop

Forms![TSO Details]![Service Notes] = ServiceNotes


TB.MoveNext
Loop
Forms![TSO Details].Refresh

TB.Close
DB.Close

Please post any suggestions.

Thank you.

 

[Marshall Barton]

I am using a recordset to retrieve data from an Access table.
The memo field in the table can hold more than 255 chars, but I
only retrieve the first 255. I have read about GetChunk and attempted
to fix the problem, but the same effect occurs. The code follows:

[snip the code]


There are several reasons why a memo field may be truncated
to 155 characters when the data is retrieved by a query.
Depends on your version of Access, but the most common are
using the memo field in a GROUP BY clause. I think the
DISTINCT predicate and UNIION verb and maybe ever ORDER BY
can also do it.

Another reason is if your form's text box for the memo field
hac something specified in it Format property.

Open the table directly from the database window to see if
the data is really there. Then check how the field is used
in queries.