Truncation of Memo fields

        2 votes: *****     8,368 views      No comments
by Allen Browne, 05 September 2006    (for ALL VERSIONS of Access)

Microsoft Access Tips for Serious Users

By Allen Browne, July 2006. Updated September 2008


Truncation of Memo fields

In Access tables, Text fields are limited to 255 characters, but Memo fields can handle 64,000 characters (about 8 pages of single-spaced text) - even more programmatically. So why do memo fields sometimes get cut off?

Queries

Access truncates the memo if you ask it to process the data based on the memo: aggregating, de-duplicating, formatting, and so on.

Here are the most common causes, and how to avoid them:

Issue Explanation Workarounds
Aggregation When you depress the  Σ  button, Access adds a Total row to the query design grid. If you leave Group By under your memo field, it must aggregate on the the memo, so it  truncates.

Choose First instead of Group By under the memo field. The aggregation is still preformed on other fields from the table, but not on the memo, so Access can return the full memo.

The field name changes (e.g. FirstOfMyMemo), so change the name and Control Source of any text boxes on forms/reports.

Uniqueness Since you asked the query to return only distinct values, Access must compare the memo field against all other records. The comparison causes truncation.

Open the query's Properties Sheet and set Unique Values to No. (Alternatively, remove the DISTINCT key word in SQL View.)

You may need to create another query that selects the distinct values without the memo, and then use it as the source for another query that retrieves the memo without de-duplicating.

Format property The Format property processes the field, e.g. forcing display in upper case (>) or lower case (<). Access truncates the memo to reduce this processing.

Remove anything from the Format property of:

  • the field in table design (lower pane);
  • the field in query design (properties sheet);
  • the text box on your form/report.
UNION query A UNION query combines values from different tables, and de-duplicates them. This means a comparing the memo field, resulting in truncation. In SQL View, replace UNION with UNION ALL.
Concatenated fields When you concatenate Text or Memo fields in a query, Access treats the result as a Text field (type dbText.) If you further process this field (e.g. combining with UNION ALL), it will truncate.

(See also Concatenated fields yield garbage in recordset.)

The first SELECT in a UNION query defines the field type, so you can add another UNION ALL using a Memo field so Access gets the idea. For example, instead of:
    SELECT ID, F1 & F2 AS Result FROM Table1
    UNION ALL SELECT ID, F1 & F2 AS Result FROM Table2;

add a real memo field first (even though it returns no records), like this:
    SELECT ID, MyMemo FROM Table3 WHERE (False)
    UNION ALL SELECT ID, F1 & F2 AS Result FROM Table1
    UNION ALL SELECT ID, F1 & F2 AS Result FROM Table2;

Note that the same issues apply to expression that are longer than 255 characters, where Access must process the expressions.

Why does it truncate?

Technically, there are good reasons why Access handles only the first 255 characters when it has to process memo fields.

String operations are both processor and disk intensive. Performance would be slower than a sloth if Access tried to compare all the thousands of characters of your memo field against all the other thousands of characters in each of potentially millions of records. Some queries would take hours or even days to complete.

If that's not enough, don't forget the comparisons are more than mere memory matching. Some data sources (e.g. Access 1 - 97 MDBs, text files) handle strings as bytes, while others (including JET 4 MDB and ACCDB files) use Unicode. Unicode needs either more disk reads or more processing to decompress, and we expect it to handle the conversions transparently and allow comparisons and joins across different types. Further, JET is case-insensitive, and the characters map differently in different language settings. And some sources need decryption as well.

The decision to handle only the first 255 characters is a perfectly reasonable compromise for a desktop database like JET.

Exports

If your query displays the memo correctly, the values are truncated when exported, you have struck another set of issues.

The list below if from Microsoft's knowledgebase. For brevity, we link just articles for Access 2000, though most issues apply to other versions as well:

Other Memo Bugs

Grouping on a memo yields garbage in some queries.

Concatenating text fields can also yield garbage in recordsets.

The original Access 2000 also had a bug that truncated long expressions in the query design grid, but this bug has been patched:


Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Truncation of Memo fields'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments


Comments require approval before being displayed on this page (allow 24 hours).