4 Common MS Access Memo Field Problems Along With It’s Fixes

Today our topic is about the most commonly rendered problems of MS Access memo field. Being an Access database user it’s very usual to encounter such issues.

But having right knowledge of fixes to resolve MS Access memo field problems will troubleshoot the issue more efficiently and quickly. Isn’t it….?

So, let’s discuss about some common MS Access memo field issues and most importantly the best fixes to resolve it.

What Is Memo In Access?

What Is Memo In Access

It is a data field which contains a variable amount of text. These texts are kept stored in a companion file, but considered as a portion of data records.

Access memo data type hold up to 65000 characters if it is entered through Access interface. And if the text is entered programmatically then it can hold up to 1 Gigabytes.

In Access 2007/2010 database, memo data type field can be used to keep Rich Text. In this type of text, formatting tasks like bold and italic can be easily done. In background section, Access converts styling of the text into HTML format.

What Is The Difference Between Text And Memo Field Type?

Microsoft Access gives two kinds of field data types: text data type, Memo data type.

These data types stores data either in text format or in combination of text and number.

Text data type:
  • This type of field data stores names, addresses, and any numbers which doesn’t need any calculations, like phone numbers, or postal codes.
  • This type of data type has the capacity to store 255 characters, but the by default the field size is about 50 characters.
  • For entering large number of characters in a Text field FieldSize property controls is been used.
Memo data type:
  • If you required to keep more than 255 characters then make use of the Memo data type. As it can store up to 65,536
  • For storing formatted text or long documents, instead of creating a Memo field just create an OLE Object field.
What’s Similar In Text And Memo Data Type?

Both of these data types store only characters which you enter in a field and excludes the space characters captured by unused positions in the field.

Sorting or grouping like operations can be performed on both text or memo field. But Access only make use of first 255 characters when you sort or group any memo field.

Problem With MEMO Field In MS Access Along With Their Fixes

Problem With MEMO Field In MS Access

Problem 1#MS Access Error Updating Memo Field With Long Text

Sometime MS Access error message also when you enter a large amount of text in a Memo field that contains an index in Access: “Run-time error ‘3709’”.

Symptoms

This problem generally occurs when you create a memo field which contains an index or when you make a field name which automatically indexes the text in Access.

At that time, you can’t enter a text which include more than 3450 characters into the field. Because when you try to edit the existing data of the field or adding more text into the field, you will be encountered with the following error message.

Run-time error ‘3709’:

The search key not found in any record.

Resolution

For resolving this issue, enter a large number of texts in the memo field and delete the index for the memo field. To know how to perform this, just follow down the steps:

  1. firstly, you need to Open the table having the Memo field in Design view.
  2. Now from the View Menu, tap to the Indexes option.
    Note: In Microsoft Office Access 2007, tap to the Design tab, and after then hit the Indexes option present in the Show/Hide
  3. Tap to the index for the Memo field, after then delete

Problem 2#MS Access Memo Field Truncated

MS Access Memo Field Truncated

Within Access Table, text fields are bounded to only 255 characters. Whereas, the Memo fields can handle up to 64,000 characters which is about 8 pages of single-spaced text.

Instead of that, sometime MS Access Memo Field Truncated or get cut off. Do you have any idea why this happen?

Well generally this type of MS Access Memo Field Truncation issue; when you process the data, which is based on memo like aggregating, de-duplicating, formatting, etc.

So, here we have listed down some common causes of Access Memo Field Truncation along with it’s fixes.

1#Issue: Aggregation      

Explanation

Pressing the Σ button, will add a Total row listed to the query design grid. Whereas, leaving the Group By under the memo field, will aggregate the memo field, and that’s the reason it gets truncate.

Workarounds

To resolve this issue, you need to select the First instead of Group By within the memo field. Now the aggregation is performed on other fields of the table. Excluding the memo, so this time access will show the complete memo without truncating it.

2#Issue: Uniqueness      

Explanation

If you have commanded your Access database to return only distinct values, using the query. Then it’s obvious that your Access will compare the memo field with all other records. Sometime this comparison causes Memo truncation in Access.

Workarounds

For resolving this, you just need to open the Properties Sheet of Query and set Unique Values to No. Or else remove the DISTINCT key word in SQL View.

Secondly you can make a new query that chooses the distinct value without using any memo. Now use it as a source for another query which recovers the memo without duplication.

3#Issue: Format property

Explanation

Format property processes field for example you are applying display in lower case (<) or upper case (>).  And to reduce the processing, Access truncates the memo field.

Workarounds

Remove all these things from the Format property:

  • the field in query design (properties sheet);
  • the text box on your form/report.
  • the field in table design (lower pane);

4#Issue:  UNION query

Explanation

UNION query actually combines the values from different tables, which ultimately duplicates the data. Thus, when the comparison of the memo field is performed, it will truncate the output.

Workarounds

For fixing this replace UNION with UNION ALL, in SQL View.

5#Issue:  Row Source      

Explanation

Memo field present in the Row Source of combo box or list box will get truncated.

Workarounds

So, to avoid this don’t make use of memo fields in combos or in the list boxes.

Problem 3# MS Access Memo Problem In Query

MS Access Memo Problem In Query

You may have seen that suddenly the content of your memo field starts appearing as control characters like “[]”. When you are using GROUP BY clause in a query on the memo field.

Do you know why this happening all of sudden? If not then also need to worry as the following work around will fix off this problem. So, just try it….

Workaround:

  •  In the query design view, change GROUP BY to FIRST in the Total row of Memo field column.
  • In the query design view, create expression in blank column and make use of left function on Memo field.

Let’s take an example, type following in the Field row present in the first blank column of the Access query:

MyMemo: Left(Notes, 10000).

Note:

At the time or sorting or grouping on Text field or on Access Memo field, only first 255 characters are used for sorting or grouping.

Problem 4# MS Access Memo Corruption

MS Access Memo corruption

It’s very common to encounter corruption of Memo Fields in Access. but don’t worry if you have already stuck in such problem. Because you have the option to fix MS Access Memo corruption and retrieve lost memo data.

For this you need to make use of Access database Repair And Recovery software which is particularly designed to repair corrupt or damaged .mdb/.accdb files. This data recovery software’s has such powerful algorithm that scans every part of a severely corrupted Access database files.

Along with that it’s highly interactive GUI  helps user to easily operate and repair Access database without doing any changes in the original structure of the file. With this recovery software you can easily restore damage or corrupt MEMO, tables, queries, indexes, password protected form and modules.

This tool can easily fix all minor and major database errors and problems of MS Access 2013, 2010, 2007, 2003, 2002, 2000 on almost all version of Windows operating system such as Windows 8/7/Vista/XP/2003. Free trial version is available that you can install to check the features and reliability of the software.

Conclusion:

Always keep the OLE data type or memo field in your Access database design. As they help to minimize the table design size by removing the unnecessary memory usage and performance glitches. You can also use string functions for handling up the memo field.

 Further Reading:

Get Quick Idea On How To Create, Edit And Delete MS Access Relationships

How To Recover Deleted Form In Access Database 2007/2010/2013/2016/2019?

3 Instant Methods To Resolve MS Access Query Corruption Issues

4 Common MS Access Memo Field Problems Along With It’s Fixes