Microsoft Access: Text vs. Memo Fields

Database programs, including Microsoft Access, require you to choose a data type whenever you create a field in a table. This data type, along with a field size and input mask (where appropriate), are an attempt by the database program to block improper data from entering into the database.

One example would be the creation of a date field. This data type would not allow you to enter the word ‘month’ – you can only enter a date. Likewise, a number field created to store inventory counts would not allow the word ‘twenty’ only the numeral ‘20’.

Novice Access users are often confused about the very similar text and memo fields.

A text data type can hold a maximum of 255 alpha-numeric characters.

A memo data type can hold a maximum of 65,536 alpha-numeric characters if entered through the user interface (i.e. a form). It can store a maximum of one gigabyte’s worth of characters if the data is being entered programmatically.

The question is why would you choose the limited text field when you can assign a memo data type and store your great American classic in one field of one record? The answer, although simple, is important when you want to query or sort your data.

You cannot search, filter, or query a memo field. [Note: a memo field can be sorted, but Access only uses the first 255 characters of data.]

The memo data type is designed to hold data that although important, is not meant to be searched. The classical example of a memo field is to record comments, such as supervisor comments on an employee evaluation. You do not search the comments, you search for the employee (or supervisor) and then review the comments stored for that employee.

The text field should be used to store data that (a) will be used for searching, filtering, or querying and (b) will not be used for calculations.