how many bytes?

[Maarkr]

a student asked me this about data storage...i read that a text field is a
string data type that uses 10 bytes plus 2 bytes per character... does this
mean that the size of the text field does not matter (five characters in a
field uses 20 bytes, no matter if the field size is 5 or 50 or 255); or does
the field size use all bytes and assigns empty values to all unused portions
of the field (a two character field size uses 10 + 2 x 2 = 14, and a 200
character field uses 10 + 2 x 200 = 410, no matter how many actual characters
are stored)? If the former is true, then why not make your text fields
default to 255 and you should never need to change it? I told him a text
string is the actual characters (including spaces) but it was just good
design to limit the field size for useability. ????

 

[Douglas J. Steele]

Access does only store as many characters as it needs to. It's easy to prove
this. Create a table with, say, 15 text fields of 255 characters each.
Access won't have any problem using that table. However, start filling in
data so that you surpass the 2000 characters/record limit, and you'll get an
error.


I don't actually believe there's is the ten bytes overhead for Text fields:
I believe that's the overhead for Memo fields.

 

[Tom van Stiphout]

On Thu, 24 Jan 2008 11:31:00 -0800, Maarkr

I think the text data type (unlike memo) does not need 10 bytes
overhead. If I had to guess it would be just 1 byte: the length of the
variable-length string (like in Pascal-style strings), or
alternatively the closing 0x00 character (like in C-style strings)

It is good design practice to limit the field size to what's needed,
so fields are not abused. Example: a US phone number can be stored in
a text field of length 10. If you allow 255, the user may enter:
"602-555-1212 x123 unless it's after hours then call his cell at
480-555-2323" and that was not really an appropriate value.
This same student may be interested in only using Variant data type.
You warn against that for the same reason.

-Tom.

 

[Maarkr]

my reference for the data types is a recent MS Access book by John Viescas,
so I assumed it is accurate. However, this is a VB string data type, if that
is the same as a string that would be entered in a text field.

 

[Douglas J. Steele]

Which of John's books? Was it Access 2007 Inside Out (which he co-wrote with
Jeff Conrad)? If so, give me a page number so I can check what he's saying.

 

[Maarkr]

2003 inside out p 775
probably in 2007, lookup string data type in index

 

[Douglas J. Steele]

Nothing in the 2007 index for "string data type", but then we were talking
about the Text data type, not the String data type. (String is for
variables, Text is for fields in tables).