Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013

    Unanswered: Is there a limit on the number of memo fields you have have in a table?

    I have a client with an Access 2003 database; he wants to change 8 text fields (256 char) in a table to memo fields. I don't like the idea, but have been unable to find anything that would indicate there is a problem with so many memo fields in a table. Anyone have a reason why I should NOT do this?

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    but if there is more than one memo column it suggests to me the table design isn't right
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 15
    There are several limitations whwen working with Memo columns. Among them:
    - Columns of memo data type cannot be grouped while filtering nor is it possible to sort them.
    - You cannot add a look up property to a memo column.
    - It is not possible to set an input mask to a memo column.

    I tend to agree with healdem: needing more than one memo column in a table is often the sign that something is wrong with the database design.
    Have a nice day!

  4. #4
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Quote Originally Posted by cdu View Post

    ...he wants to change 8 text fields (256 char) in a table to memo fields...
    It sounds suspiciously like he wants to combine these individual Fields into a single, Memo Field, simply to make entering data easier, which is a terrible reason for doing this!

    One of the Cardinal rules to having a well designed, normalized database is that no single field will contain more than one individual piece of data. Data stored in Memo fields should be restricted to "notes" or narrative type information, such as a salesman's notes on a customer visit or a doctor's progress notes on a patient.

    Working primarily in a Heath Care environment, I've used Memo fields extensively for a number of years, without ever having a problem. The secret is to follow one simple rule:

    Never, never, never place data in a Memo field if there any possibility that you will ever need to search, sort, parse or in any other way manipulate the data!

    Because many queries perform some of these functions, you have to take care in using them in queries or they will be truncated to 255 characters. Allen Browne has some excellent advice explaining how to deal with Memo Fields and Queries:

    Microsoft Access tips: Truncation of Memo fields

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts