Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Limitations ( and advantages ) of MEMO fields

Tables and Relationships

Limitations ( and advantages ) of MEMO fields

by  LittleSmudge  Posted    (Edited  )
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.



Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top