Memo type field size

[John]

I created a database using MS Access 2007 and made it compatible with the
older version since most of the computers at the office still uses the older
version.
Now, when creating a "Report" that is linked to a "Memo" type field, only
255 characters are visible although more than that were entered in the "Form".
I've checked the type of the field from the "Table" and the type was "Memo".
What I know is that a "Memo" type field can hold up to 2GB of information
per record. But it seems when you make it compatible with the older version
only 255 are remain visible on the "Report".
Hope anyone could help me on this. Thanks

[Linq Adams via AccessMonster.com]

I assume that your form is based on a multi-table query. Many types of
queries cause memo fields to be truncated to 255 characters. HAve a look at
Allen Browne's article on the whys along with some workarounds for the
problem:

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200905/1

[4110]

I have a problem similar to John's. I am using Access 2003. I have a memo
field in a table. The memo field is truncated to 255 characters in a report.
Originally I fed the report from a query that in turn was based on the
table. I worked through the suggestions in Allen Browne's article without
success. I then tried to determine where the problem originated and created
a report that read from the table directly (no query). Same problem. Is
there a way to display a complete memo field on a report?

Thanks,

David

[John W. Vinson]

On Mon, 27 Jul 2009 07:04:01 -0700, 4110 <41...@discussions.microsoft.com>
wrote:

>I have a problem similar to John's. I am using Access 2003. I have a memo
>field in a table. The memo field is truncated to 255 characters in a report.
> Originally I fed the report from a query that in turn was based on the
>table. I worked through the suggestions in Allen Browne's article without
>success. I then tried to determine where the problem originated and created
>a report that read from the table directly (no query). Same problem. Is
>there a way to display a complete memo field on a report?

There's something specific causing this problem: memo fields do indeed display
in their entirity in reports.

Some things to check:
- The Report's Recordsource should NOT be sorted or grouped on the memo field;
check both the query and the report's Sorting and Grouping dialog
- If the recordsource is a UNION query it must be UNION ALL
- There should be no Format (not even > to display in all caps) on the memo
field, either in the table definition, the query definition, or the report
textbox
--

John W. Vinson [MVP]

[KenSheridan via AccessMonster.com]

This has been a long standing problem, certainly since Access 95 if not
before. It frequently manifests itself if a memo field has been added to a
form or report either by dragging from the field list or by using the form or
report wizard to create the form or report. In my experience it can
generally be resolved by deleting the bound control from the form or report,
adding a text box control from the toolbox and setting its ControlSource
property to the name of the memo field. In a form you'd probably also want
to set the control's EnterKeyBehavior property to 'New Line in Field'.

The behaviour can also be experienced in raw datasheet view of a table with
data imported from other applications. I first came across it with data
imported from dBASE files. The data is there and can be seen in forms or
reports set up as described above, but is truncated in datasheet view of the
table.

Ken Sheridan
Stafford, England

4110 wrote:
>I have a problem similar to John's. I am using Access 2003. I have a memo
>field in a table. The memo field is truncated to 255 characters in a report.
> Originally I fed the report from a query that in turn was based on the
>table. I worked through the suggestions in Allen Browne's article without
>success. I then tried to determine where the problem originated and created
>a report that read from the table directly (no query). Same problem. Is
>there a way to display a complete memo field on a report?
>
>Thanks,
>
>David
>

>> I assume that your form is based on a multi-table query. Many types of
>> queries cause memo fields to be truncated to 255 characters. HAve a look at
>> Allen Browne's article on the whys along with some workarounds for the
>> problem:
>>
>> http://allenbrowne.com/ser-63.html

--
Message posted via http://www.accessmonster.com

[4110]

Thanks guys,

I followed your suggestions with mixed results.

The good news is that I did get a complete memo field to display on a report.

The bad news is that it didn't happen reliably or gracefully.

To simplify things I created a new table with a memo field. Then I created
a new report with one text box. The RecordSource for the report was the
table. John, I did follow your suggestion to add the text box and then set
the ControlSource to the memo field in the table.

I was able to get the text box to display the complete memo field sometimes.
But other times it would only display 255 characters. What seemed to be
critical was how I populated the table. I had my original table with many
records. For this test, I used a record with a value that had about 700
characters. Here is what I tried:

Type a value into the memo field in the new table.
Report result - complete field.

In the old table, tab into the field, copy, then switch to the new table and
paste the value.
Report result - partial.

In the old table, Click on the field, go to the beginning of the field,
carefully highlight the field until the last character is visible and
highlighted; copy; switch to the new table and paste.
Report result - complete field.

Create a record with a Make Table query.
Report result - partial.

Delete the record in the table. Add a new record with an Append query.
Report result - partial.

Additional note. The complete value is present in the table in all of these
cases. In other words, it isn't truncated in the table. It is possible to
click into the field in the table and work to the end of the record. It is
always complete. However, something about the way the field is populated
impacts what happens in the report. So there is a workaround, but it is
impractical or impossible to implement.

Is there a solution?

Thanks,

David

[KenSheridan via AccessMonster.com]

The only other thing I can suggest is that you include a hidden control bound
to the memo field in the form, then in the Detail section's Format event
procedure assign the value of the hidden bound control to a visible unbound
control:

Me.YourUnboundControl = Me.YourBoundControl

This works for me. In fact If I show both controls the bound one shows
truncated data, the unbound one the full data. BTW, unlike in a form, you
cannot assign the value of an underlying column directly to a control in code
in a report's module; you can only reference a control in the code, not the
underlying column directly.

Ken Sheridan
Stafford, England

>> This has been a long standing problem, certainly since Access 95 if not
>> before. It frequently manifests itself if a memo field has been added to a

>[quoted text clipped - 32 lines]

[4110]

Hi Ken,

Thanks for your help.

I created a new report and made the RecordSource the old table. On the
report I put two text boxes. I made the Contol Source for one the memo field
from the table. I didn't assign a ControlSource for the other text box. I
added the following code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Text4 = Me.Text2
End Sub

Alas, both text boxes displayed the truncated results. So unless I messed
up somewhere it didn't work for me.

Is there anything else I can try?

David

[KenSheridan via AccessMonster.com]

Its overkill and not very efficient, but one way maybe worth trying is to get
the contents of the memo field chunk by chunk with the GetChunk method and
assign the value to an unbound control in the report. To do this you'd first
establish a recordset object which returns the current record, so the code in
the Detail section's Format event procedure would be like this, first having
made sure you have a reference to the DAO object library:

Const conChunkSize = 1024
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String
Dim lngOffset As Long
Dim lngTotalSize As Long
Dim varMemo As Variant

strSQL = "SELECT MemoField FROM MyTable " & _
"WHERE MyID = " & Me.MyID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Set fld = rst!MemoField

lngTotalSize = fld.FieldSize
Do While lngOffset < lngTotalSize
varMemo = varMemo & fld.GetChunk(lngOffset, conChunkSize)
lngOffset = lngOffset + conChunkSize
Loop

Me.txtMemo = varMemo

The report's detail section should include an unbound textbox, txtMemo,
allowed to grow, and a hidden control MyID bound to the primary key or some
other uniquely valued column in the report's underlying recordset. If the
key is multi-columned you'd have to include controls for them all and
reference them all in the SQL statement's WHERE clause in a Boolean AND
operation.

If that doesn't do it I can't see that anything will.

Ken Sheridan
Stafford, England

4110 wrote:
>Hi Ken,
>
>Thanks for your help.
>
>I created a new report and made the RecordSource the old table. On the
>report I put two text boxes. I made the Contol Source for one the memo field
>from the table. I didn't assign a ControlSource for the other text box. I
>added the following code:
>
>Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
> Me.Text4 = Me.Text2
>End Sub
>
>Alas, both text boxes displayed the truncated results. So unless I messed
>up somewhere it didn't work for me.
>
>Is there anything else I can try?
>
>David
>

>> The only other thing I can suggest is that you include a hidden control bound
>> to the memo field in the form, then in the Detail section's Format event

>[quoted text clipped - 67 lines]

[4110]

Ken,

I like it. Excellent approach.

Unfortunately, while there is encouragement and an improvement, there is
still a bit of a problem. The largest record is truncated slightly. It is
not at the 255 character limit. In fact, it displays about 710 characters
(of a total of about 741) so I suspect it is something in the code. Here's
some info. If I monitor the code, the Total Size for the longest record is
1482. It goes through the loop twice. The Chunk size is 1024 so it looks
like twice should be enough but it misses those last few letters.

Hope that is enough information to diagnose the porblem.

David

[KenSheridan via AccessMonster.com]

The length of the data in the memo field shouldn't matter; it should loop
until it gets it all. That's really the raison d'être of the GetChunk method,
breaking the data down into manageable portions. I've no idea why you are
missing the final characters. Try increasing the chunk size to 2MB:

Const conChunkSize = 2048

so it gets it in one iteration of the loop. Alternatively try lowering it to
255 bytes and see what happens then. I'm flying by the seat of my pants on
this one as I've no real idea what's causing the problem.

Ken Sheridan
Stafford, England

4110 wrote:
>Ken,
>
>I like it. Excellent approach.
>
>Unfortunately, while there is encouragement and an improvement, there is
>still a bit of a problem. The largest record is truncated slightly. It is
>not at the 255 character limit. In fact, it displays about 710 characters
>(of a total of about 741) so I suspect it is something in the code. Here's
>some info. If I monitor the code, the Total Size for the longest record is
>1482. It goes through the loop twice. The Chunk size is 1024 so it looks
>like twice should be enough but it misses those last few letters.
>
>Hope that is enough information to diagnose the porblem.
>
>David
>

>> Its overkill and not very efficient, but one way maybe worth trying is to get
>> the contents of the memo field chunk by chunk with the GetChunk method and

>[quoted text clipped - 64 lines]
>> >> >> >>
>> >> >> >> http://allenbrowne.com/ser-63.html

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200907/1

[4110]

Ken,

It works!

I noticed a glitch in my data this morning so it was OK yesterday.

BTW, I moved your code to a module so it is available for all reports.

Thank you very much for your help.

David

[4110]

Ken,

Your solution has been working brilliantly for me and my colleagues who use
Access 2003. However, a person in another city has Access 2007 and they had
a problem. The report is fine when it is opened in Access; the text box on
the report displays all of the text. However, when they export to an rtf
file the text is truncated. Again, it exports OK in 2003. It only truncates
on export in 2007. Is there a solution to this problem?

Thanks,

David

[KenSheridan via AccessMonster.com]

David:

Not using Access 2007 myself I don't think I can help you in this case. Do
they need to edit the .RTF file? If not an alternative might be to output
the report to a .PDF file using something like the free CutePDF Writer from:

http://www.cutepdf.com/

As it just sends the file to a virtual printer, if the report is opening with
the memo fields intact the .PDF file should also keep them intact. An added
bonus is that the .PDF file keeps any graphical elements of the report which
a .RTF loses.

Ken Sheridan
Stafford, England

4110 wrote:
>Ken,
>

>Your solution has been working brilliantly for me and my colleagues who use
>Access 2003. However, a person in another city has Access 2007 and they had
>a problem. The report is fine when it is opened in Access; the text box on
>the report displays all of the text. However, when they export to an rtf
>file the text is truncated. Again, it exports OK in 2003. It only truncates
>on export in 2007. Is there a solution to this problem?
>
>Thanks,
>
>David
>

>> The length of the data in the memo field shouldn't matter; it should loop
>> until it gets it all. That's really the raison d'être of the GetChunk method,

>[quoted text clipped - 31 lines]
>> >> >> >> >>
>> >> >> >> >> http://allenbrowne.com/ser-63.html

--
Message posted via AccessMonster.com

http://www.accessmonster.com/Uwe/Forums.aspx/access/200908/1