INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Tables and Relationships

Limitations ( and advantages ) of MEMO fields by LittleSmudge
Posted: 18 Sep 03 (Edited 15 Aug 05)

During a discussion on the limitations of memo fields I looked - but could not find - a comprehensive list within MS-Access Help files.

So I've created my own.
Caveat : This is written based on Access 2000.
I believe that there is the possibility of sorting on the first 255 characters in A2k2, but I haven't tried it.


64k characters in length ( usually )
65,535 when entering data through the user interface;
(If the Memo field is manipulated through DAO and only text and numbers [not binary data] will be stored in it, then the size of the Memo field is limited by the size of the database.)

Can't be sorted : Either in table view or on a Form
( If you click on a text box control bound to a memo field the A-Z & Z-A sort order tools on the tool bar gray out. )

No Input Mask : The memo field does not have an input mask property in table design view and text box control bound to memo field will not accept input masks in form design view.

Can't Index : A memo field cannot be Indexed

Can't be a Lookup Field : ( This is a good thing actually, as Lookup fields are a pain and have no place in 'professional' database schemas )

Aggregates and Group By etc.
Memo fields can't be used in Aggregate Arguments ( like Max, Var, Sum etc. )
If used in 'Group By' totals in a query only the first 255 characters are returned.
'Having' and 'Where' clauses in Group Aggregate functions also return only the first 255 chars
However, using 'First' or 'Last' arguments return the full length of the string.



Caveat : This is written based on Access 2000.
I believe that there is the possibility of sorting on the first 255 characters in A2k2, but I haven't tried it.




Back to Microsoft: Access Tables and Relationships FAQ Index
Back to Microsoft: Access Tables and Relationships Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close