Why does the Field Size of a Textbox have to be declared?

[jora]

An Access text field can contain a maximum number of 255 characters. Why is it important to assign a field size to text fields and why do you think its only 255 characters?

[tdw]

Being a non-expert I can only tell you what my understanding is, so if anyone wants to correct me or expand on it please do.

My understanding is that limiting the number of characters available to a text field is for reducing it's size, which helps make the overall size of the file smaller, and helps speed up queries. If you need a text field with more characters, then you need to make it a memo field instead of a text field. However, I've been reading about dangers of corruption associated with memo fields. I use a memo field and have had no problems.

[missinglinq]

As tdw has stated, if you need a field to hold more than 255 characters you need to use a Memo field, which can hold in excess of 64K. The problems with corruption that tdw cited were with earlier versions of Access (v97 and earlier, I believe) and have long since been resolved.

If a database is set up properly, text fields seldom need to be more than 255 characters long. One of the Cardinal rules to having a well designed, normalized database is that no single field will contain more than one individual piece of data. Data stored in Memo fields should be restricted to "notes" or narrative type information, such as a salesman's notes on a customer visit or a doctor's progress notes on a patient.

Working primarily in a Heath Care environment, I've used Memo fields extensively for a number of years, without ever having a problem. The secret is to follow one single rule:

Never, never, never place data in a Memo field if there any possibility that you will ever need to search, sort, parse or in any other way manipulate the data!

Because many queries perform some of these functions, you have to take care in using them in queries or they will be truncated to 255 characters. . Allen Browne has an excellent article explaining how to deal with Memo field and queries:

http://allenbrowne.com/ser-63.html

Welcome to Bytes!

Linq ;0)>

[tdw]

That's exactly how I use memo fields as well: special instructions and notes about a particular order. The only kind of "searching" that occurs in that field is by using the Find/Replace feature in the menu bar.

[mshmyob]

As indicated one of the reasons for assigning a size to a text field is for size of database file. It is one of the same reason we chose either byte, integer, double, or long.

In a text field each character requires 2 bytes. If you only think the data in that field will be 25 characters long at most then set it to 25 so that it will only allocate 50 bytes as opposed to 510 bytes. Now expand that and assume you have 20,000 records. Multiply by 20,000 and see the difference.

As to why 255. It is really 256 since no value counts. I assume 256 would allow each member of the basic character set to be stored to the field.

I think the memo thing has been fully explained by Linq.

cheers,