IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: Access Database Repair Service
An in-depth repair service for corrupt Microsoft Access files
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
IN THE SPOTLIGHT: Code Protector
Standard compilation to MDE/ACCDE format is flawed and reversible.
Microsoft Access Tips for Serious Users |
By Allen Browne, July 2006. Updated September 2008 |
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?
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:
|
| 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.
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.
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:
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: 
This is a cached tutorial, reproduced with permission.
iTech Masters | VAT: GB202994606 | Terms | Sitemap | Newsletter
Twitter
