Today our topic is about the most commonly rendered problems of the MS Access memo field. Being an Access database user it’s very usual to encounter such issues.
But having the 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 some common MS Access memo field issues and most importantly the best fixes to resolve it.
What Is Memo In Access?
It is a data field that 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 holds up to 65000 characters if it is entered through the Access interface. And if the text is entered programmatically then it can hold up to 1 Gigabyte.
In the Access 2007/2010 database, the 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 the background section, Access converts the 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 store data either in text format or in a combination of text and number.
Text data type:
- This type of field data, store names, addresses, and any numbers which don’t need any calculations, like phone numbers, or postal codes.
- This type of data type has the capacity to store 255 characters, but by default, the field size is about 50 characters.
- For entering a 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 in which you enter 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 makes use of the first 255 characters when you sort or group any memo field.
Problem With MEMO Field In MS Access Along With Their Fixes
Problem 1# MS Access Memo Corruption
It’s very common to encounter the corruption of Memo Fields in Access. but don’t worry if you have already stuck in such a 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 has such a powerful algorithm that scans every part of a severely corrupted Access database file.
Some Powerful Features Of This Recovery Software:
- It’s highly interactive GUI helps users 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 damaged or corrupt MEMO, tables, queries, indexes, password-protected forms, and modules.
- This tool can easily fix all minor and major database errors and problems of MS Access 2019, 2016, 2013, 2010, 2007, 2003, 2002, 2000.
- Support almost all versions of Windows Operating Systems such as Windows 10/8/7/Vista/XP/2003.
- A free trial version is available that you can install to check the features and reliability of the software.
Steps To Use MS Access Repair and Recovery Tool
Problem 2#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’”.
This problem generally occurs when you create a memo field that 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 includes more than 3450 characters in 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’:
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:
- firstly, you need to Open the table having the Memo field in Design view.
- 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
- Tap to the index for the Memo field, after then delete
Problem 3#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 are about 8 pages of single-spaced text.
Instead of that, sometimes 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 memos like aggregating, de-duplicating, formatting, etc.
So, here we have listed down some common causes of Access Memo Field Truncation along with its fixes.
Issue no. 1: Aggregation
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 truncated.
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.
Issue no. 2: Uniqueness
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. Sometimes this comparison causes Memo truncation in Access.
For resolving this, you just need to open the Properties Sheet of Query and set Unique Values to No. Or else remove the DISTINCT keyword in SQL View.
Secondly, you can make a new query that chooses a distinct value without using any memo. Now use it as a source for another query that recovers the memo without duplication.
Issue No 3: Format property
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.
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);
Issue No. 4: UNION query
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.
For fixing this replace UNION with UNION ALL, in SQL View.
Issue No. 6: Row Source
Memo field present in the Row Source of a combo box or list box will get truncated.
So, to avoid this don’t make use of memo fields in combos or in the list boxes.
Problem 4# 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 workaround will fix off this problem. So, just try it….
- 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 the blank column and make use of left function on the 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).
At the time or sorting or grouping on the Text field or on the Access Memo field, only the first 255 characters are used for sorting or grouping.
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.