Memo vs Text Field size

[Doug Sanders]

A text field set to 255 bytes is that, but if there is no data in a Memo
field, how much space does it take?

I'm looking for tradeoffs on a seldom used field that might be large at
when used.

Thanks

Doug Sanders

 

[John W. Vinson]

A text field set to 255 bytes is that, but if there is no data in a Memo
field, how much space does it take?

Actually, a Text field occupies only the space actually used plus 4 bytes of
overhead, not 255 bytes. A Memo field has 16 bytes of overhead, regardless of
the amount of text used (the actual memo contents aren't stored in the table
but elsewhere within Access).

I'm looking for tradeoffs on a seldom used field that might be large at
when used.

It's not that big; the downsides of memos are the facts that you can't index
or sort them, searching is possible but inefficient, and they're more prone to
corruption. On balance though they are perfectly reasonable if used when
they're needed.

John W. Vinson [MVP]

 

[Guest]

Also, in queries memo fields get truncated at 255 characters. There is a
way to work around that however. But keep this in mind when you aren't
getting all you data initially in a query of from a report.

 

[Rob Parker]

That's not strictly correct. A memo field will be truncated to 255
characters if the query involves an aggregation function (eg. GROUP BY or
domain aggregate function such as dLookup, etc); if it has only SELECT
operations there is no truncation.

Rob

 

[Guest]

A text field set to 255 bytes is that

Only if you use DDL to create a field of type CHAR.
But fixed width text fields (CHAR) are not supported
in Access, so you wouldn't do that unless you were
planning to do all of your table design and data access
using DDL and SQL.

Note however that, if you use SQL, mdb TEXT fields
do not automatically truncate white space (unlike SQL
Server varChar) . In Access, truncation of white space
is done (automatically) by the form or datasheet, not by
the database.

(david)

 

[missinglinq via AccessMonster.com]

I've used memo fields for a number of years without a single problem, and to
John's statement "On balance though they are perfectly reasonable if used
when they're needed" I would add they're perfectly reasonable if used AS
INTENDED! They're designed to hold memos; in other words notes! They're not
intended to be used to hold data that will ever need to be manipulated. As
noted, a Select query creates no problem, but almost any other type of query
does.

 

[David W. Fenton]

the downsides of memos are the facts that you can't index
or sort them, searching is possible but inefficient, and they're
more prone to corruption. On balance though they are perfectly
reasonable if used when they're needed.

The problems from corruption can mostly be ameliorated by editing
the memos unbound and by keeping them in a separate memo table, one
memo to a record.

 

[David W. Fenton]

A memo field will be truncated to 255
characters if the query involves an aggregation function (eg.
GROUP BY or domain aggregate function such as dLookup, etc); if it
has only SELECT operations there is no truncation.

Won't it truncate if you sort on the memo field, too?

 

[John W. Vinson]

The problems from corruption can mostly be ameliorated by editing
the memos unbound and by keeping them in a separate memo table, one
memo to a record.

So I understand. I've never had much trouble with just including a memo field
in a main table... though I'll often have a [Notes] table with nothing but a
FK and a memo (and occasionally a date/time field defaulting to Now()).

John W. Vinson [MVP]

 

[Guest]

You need to be careful with this kind of thing. Last I looked
there was still a bug that returned garbage in memo fields if
you had more than one table and grouped, sorted or unioned
a memo field.

(david)

 

[Rob Parker]

Not in my experience.

But I use memo fields for large text notes, and that's rarely, if ever, a
field that I (or my users) would be wanting to search on.

Rob

 

[David W. Fenton]

The problems from corruption can mostly be ameliorated by editing
the memos unbound and by keeping them in a separate memo table,
one memo to a record.

So I understand. I've never had much trouble with just including a
memo field in a main table... though I'll often have a [Notes]
table with nothing but a FK and a memo (and occasionally a
date/time field defaulting to Now()).

The big advantage of the separate table is that any damaged memo
pointers are in a different table, so no other data is involved.

And, of course, in the case of worrying about how much space is
used, if there's no memo, then there's no record in the other table,
so no space at all is used in that case (not that the amount of
space used by an empty memo field pointer is significant).

 

[Guest]

What is the work around? I am having issues with memo fields truncating at
255 characters when used in a UNION query.

 

[Ken Snell \(MVP\)]

Use a UNION ALL query instead of a UNION query.