Limitation of Memo fields

[LittleSmudge]

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. Have I missed anything folks ?

64k characters in length

Can't be sorted : Either in table view of the 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 grey 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 have 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 )



.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[jrbarnett]

Yes - can't be used to Group By in queries.

John

 

[willir]

LittleSmudge

While reading some stuff on the new version of Access, I came across a comment that you can now search the first 255 characters of a memo field.

Since I do not have Access 2002, I have not tested this, but it would be a very useful feature it this is true.

Doesn't Access store a memo field differently too -- uses a similar methodology as storing an internal blobs and images -- they are not stored on the table, but are referenced by a pointer???

This will be a great tip / FAQ when completed.

Richard

 

[LittleSmudge]

Yes Richard , the thought of developing this into an FAQ was hovering around in the back of my mind too.

I'll take your hint and gather it all together later.

Thanks to John for the Group-By limitation ( Can't think why I missed that one ! ) Well done John.



Graham

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[jrbarnett]

I had heard willir's comment that Memo fields are stored elsewhere to others as well, but wasn't 100% sure about it so didn't mention it.
If this is the case, then in addition to the Group by limitation, I don't think it would be possible to use them in a Where or Having clause in a query either, but this should be tested before writing up.

John

 

[LittleSmudge]

Okay John, so here are my results from the 'experiment' you've just inspired.

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.



& a clarification on SIZE limit
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.)

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.