Memo vs Text Record Record Length

[bradmaunsell]

Can anyone tell me the pros and con of using a text field vs a memo field regarding to record length and performance?

I am writing a general ledger system and createing a transaction table that includes:

TrxDate
GL_Account
TrxNote (Text vs Memo)
DR
CR
etc.

Will the system have a shorter record length and better performance if I use a text field vs. a memo field for "TrxNote"?

Any suggestions welcome.

Thanks,
Brad

 

[LittleSmudge]

The answer is - it all depends.. ..

Text field is a predefined limit ( good because it stops the user waffling on and on and on etc.. )
Memo fields are ( in effect ) unlimited in lenght. Memory usage is 2 bytes per character stored.

Time was when a 150 character text field ALWAYS took up 150 bytes in memory regardless of the number of characters actually stored in the field. However this doesn't happen now. It is 2 bytes per character stored.

The real issue is if your users will want to search the field for a character string, or if you'll ever use the field in a Distinct query, or you want to sort by the data in that field then TEXT is the only way to go.




G LS

 

[bradmaunsell]

I knew the limited/unlimited issue but not the 2 byte text storage.

Sounds like TEXT is clearly the best choice for this app.

Thanks,
Brad
South Burlington, VT

 

[LittleSmudge]

2 bytes bacause they are now stored as unicode two byte pairs - This is so that Billy boy can sell is products in ever more obscure parts of the world that don't just use the 26 English language character set !

There is in effect no difference in memory usage between the two now ( for char lengths < 256 )
The real issue is Grouping, Sorting, Indexing, Searching, Filtering - All impossible with MEMO


G LS

 

[bradmaunsell]

Yes, I understand. In this case, there may be an occatrional need to search the text for &quot;something&quot;.

 

[WildHare]

Umm.. You CAN search in a memo field.

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm