|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Short Text v. Long Text By Richard Rost 
6 years ago
Short v. Long Text. What's the Difference (also Memo Fields)? In this video we'll take a look at the differences between Short Text and Long Text fields, the benefits and drawbacks of each, when you should use each type, and we'll see how Long Text fields have been updated since the days of Memo fields in older versions of Access. Brandon from Vermont (a Silver Member) asks, "I know in your classes you say to use Short Text for most fields and Long Text just for comments, but what are all the differences between the two, and when should I use each?" MembersThere is no Extended Cut for this video, but watch the Extended Cut for the Compact Importance video! LinksCompact Importance: https://599cd.com/CompactImport
IntroIn this video, we will talk about the differences between short text and long text fields in Microsoft Access, including when to use each type, the advantages and drawbacks of both, storage limits, field properties, and important considerations like efficiency, potential corruption, and limitations when using long text in forms and queries. We'll also discuss how features like rich text formatting, append-only mode, and indexing have changed over the years, and why it's best to use short text fields for most purposes except comments or notes.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we're going to talk about short and long text, when to use short text or long text, formerly called memo fields, in Microsoft Access. Brandon from Brattleboro, Vermont, a Silver Member, asks: I know in your classes you say to use short text for most fields and long text just for comments. But what are all the differences between the two and when should I use each? Well Brandon, if you've got a text field, chances are you're better off using short text for it unless you're certain it's going to be longer than 255 characters. So anything from names to addresses to phone numbers to pretty much everything I could think of except comments or notes should be stored in a short text field. Long text fields have a lot of drawbacks, and they're not very efficient. I'll show you why in just a minute. There are lots of different data types in Access, and it's important to know which type to use for what kind of data: currency values, date/time values, yes/no values. So knowing the difference between short text and long text can be just as important. Let's start with how much information you can put in each type. Short text can store from 0 to 255 characters. Usually it's used for small things: first name, last name, address, phone number. Yes, phone numbers should be in a text field, not a number field. Only put number field data in when you have something you're going to be doing calculations on. You're never going to be adding two phone numbers together, so that's a text field. Long text can store up to 64K, 64 kilobytes. That's 65,535, I think it is. There's a lot of information - 64,000 characters roughly. In the Access interface, if you type it in or edit it in a table or form, you can store up to one gigabyte of data. That's a lot more if you do it with programming, if you do it in code. I don't recommend it, but you can do it. But for all intents and purposes, it's 64K. So it's a good amount of text to type in things like notes for a customer, directions how to get someplace, recipes, things that you're sure are going to be more than 255 characters. But don't plan on storing gigabytes of data because the maximum size of an Access database file is two gigabytes. If you've got more than a few thousand characters, I strongly recommend storing the data in an external file. That's a whole different lesson. With short text, you can limit the field size. You can say I want states to be only two characters, for example. With long text, you can't do that. Short text fields contain plain text only. Long text fields can store rich text with formatting, colors, and so on. So again, for your notes, if you want to add bold or italics or change things like that, you can store that in a long text field. When you add these as fields on a form, a short text will usually come in as a single-row text box, whereas long text will come in as a multi-row text box with a scroll bar. Of course, those are just defaults. You can change that. The Enter key behavior: if you press Enter in a short text field, it'll go to the next field, whereas Enter in a long text field defaults to going to a new line in the text box. Again, those are settings that can be changed at the form level. Long text fields have an often-overlooked feature called append only. I overlook it a lot because I never use it, but you can set a long text field to be append only. When the user goes to that record, they can only add to the end of the long text field. They can't change what's already in there. Again, I never use it. Usually things like that are for tracking contacts or adding notes, and I would make another record in a related table to store stuff like that. One important thing about short text fields is that you can use them in combo boxes and list boxes. When you drop that list down to pick someone, you can't put long text values in combo boxes or list boxes. So that limits them there. Short text fields are much more efficiently stored in the database as far as the size of your database goes. Long text fields, not so much. I recently built a database to test short and long text fields. I added a million records of different sizes. Then I checked the file sizes both before and after compacting the database. As you can see right here, the two that I selected - a million records with 255 characters in them - the file size with short text fields turned out to be 273 MB roughly. With long text fields, it's 316 MB. It's a much larger file to store the same information. So use long text fields sparingly unless you've got really, really large fields. If you don't have fields that you're sure are going to be more than 255 characters, try to stick with short text if possible. If you're concerned about file storage, remember, a big database is a slow database. I've got more on this database that I built for this analysis in a few minutes. Some problems with long text fields: they can become easily corrupted. Access really isn't a great repository for storing large bits of information like a huge Word document, for example. So if you've got fields with lots and lots and lots of data in them, your database is more likely to be corrupted than if you're using short text fields. That's both from the theory that I've read and from my personal experience. Every time I've had a problem with a database getting corrupted, it's because of a long text field. Your data can easily be truncated. I've had this happen too. I've had situations where people went to edit their data and somehow it got truncated or chopped off. I don't know whether it's a bug in Access going back many, many years or if it's just something that randomly happens, but I've seen it happen at least a dozen times. Database is running perfectly fine. No warning of any kind of error as far as needing to repair the database, just the data that they knew was in there was gone. The field just got chopped in half. So again, that's another reason why it's very important to back up your data. I keep this slide on hand for situations like this. It's not only important to back up nightly so you have yesterday's backup, you should be saving backups going back. Have a backup from the last couple nights. Have a backup from a week ago. Have a backup from a month ago. I store a backup for every month going back a year. Then I store every year's backup. I've had customers where they lost data and didn't realize they lost data three months ago. So just having a couple of nights of backups is not enough. You need to be able to version that stuff and go back and say, well, this record was this in September, it's supposed to be that, it's now February, and we have to go back to September's backups. Make sure you store backups and keep versions of them related to truncated data. If you've got data that's been imported with code, like I said before, you can have up to a gigabyte of data in that long text field. If you then go to try and edit it in your form, it's going to truncate it right off at 64K. Again, truncation is a problem. Access doesn't handle really, really large fields that well. Like I said, some notes, some comments, a record of what you talked about, directions how to get somewhere - that's okay. Don't try to store 800 pages of information in a single memo field. If you try to use aggregate queries with long text fields (those are things where you do like sums and group bys and such, and some other things, some types of SQL like union queries), if you include a long text field in any of that stuff, it will get truncated and sometimes corrupted, and sometimes extra characters get added to it. So again, notes and memos only, that's fine. Don't put anything in a long text field that you're going to have to do any kind of reporting on or relating to something else or group bys or any of that stuff. If it's anything other than notes for you to just sit there and read, human read, not the computer having to analyze it, then you can put it in a long text field. If you think you're going to want to be grouping on it and searching on it and storing it and all kinds of crazy stuff, don't do it. Now, as bad as long text fields can be, there have been some improvements and changes since they used to be memo fields. Now, I learned this stuff back in the 90s. That's when I first started learning Access. Some of those habits and things stuck with me. In some of my classes, you might hear me say things like long text fields can't be sorted. Now they can. They've changed that in recent versions of Access. I'm not exactly sure when; some of this was news to me. You can now search, sort, and filter on a long text field. You shouldn't have to, because that's not the kind of stuff that you should be sorting on. But if you want to do a search through all of your long text fields and look for a word, for example - what if you're storing brief synopses of stories that you've read. Well, now you can search inside your long text fields. Indexing: memo fields couldn't be indexed. I was quite surprised to find that long text fields now can be indexed. Again, I can't think of a reason why you'd want to, but you can. Indexing is designed to speed up searches and sorts, so I guess they figured, well, since they can, they did. Again, I can't fathom why you'd want to. As I mentioned in the last slide, memo fields couldn't be used in aggregate queries before. That's where you take sums and group bys and mins and max and stuff. Now you can. Again, why, I don't know, but you can. I did read on one page during my research that aggregate queries, searches and filters, and stuff will only look at the first 255 characters. I have not personally verified this, but that's what I've read on a couple of different sources. Again, you shouldn't be doing these kinds of things with long text fields anyway. So, that's pretty much it. The difference between short text and long text. Again, for most fields, I stick by my original viewpoint that you should be using short text for most fields, except for your random notes. You can put those in long text. If you're going to be collecting a lot of notes on someone over a regular period of time, if you want to store notes whenever you talk to a customer, don't put it all in one long text field. You can, but you shouldn't. You should use a second related table to store contact information, and every time you talk to them, make it a separate long text field in a related table. Don't just store all of it in one customer long text field. You will eventually get to the point where that field is unmanageable and very difficult for the database to work with. If you do a second related table, a contacts table, for example, you can store the date and time of the contact, how long the call went, all that information in the relevant fields, and then just put the notes in a long text field. Long text fields are good. They have their place. Just don't overuse them, and don't rely on them for anything other than simple human-read comments and notes. Now, where did I get those efficiency stats from? I built a compact and repair database that did a whole bunch of analyzing for me. I actually recorded it; it's about three hours long, the building of this monster database, and it's a video that's set for members only that's going to be released very shortly here, if not by the time you watch this. Basically, I built one database to add records to tables. You specify the number of records and the length of each record, and it just went and looped. Another database would open the second database, set its parameters, let it run, and then close it, analyze it, compact it, and do all that stuff. So if you want to learn a whole bunch of crazy stuff, it's a three-hour long video. It covers the Sleep function, opening another database, waiting for that database to exit, compacting with VBA, reading and writing text files (we use text files for the settings files so the one database knows what the other one wants), writing file sizes, deleting and renaming files, recordsets to add the records. Three hours of insanity because it's just me pulling my hair out wondering why things are happening the way they are. I've always been taught a certain way by the books I've read and the classes I've taken and taught. This is how you do it. Well, now that I'm more of the expert, I want to know these things for myself. So I built a database to test the efficiency of short text and long text and test the efficiency of compact and repair. I've got a whole bunch of interesting information. Next, I'm going to be putting together a free TechHelp video on compact and repair, which is where this slide came from. If you're interested in all this crazy stuff, that's for my members. How do you become a member? You click on that Join button right under the video and you'll see a list of all the different options for the different levels of support. Of course, my TechHelp videos like this one will always be free. I'm going to keep making them as long as you keep watching them. If you like this video, make sure you give it a like and share it. Of course, hit the Subscribe button and click the little bell so that you get notifications whenever I release a new video. If you have not yet checked out my free Access Level 1 lesson, it's three hours long and it's absolutely free. It's on my website and on YouTube. If you like Level 1, Level 2 is just a dollar and that also is free for members. If you want to have your question answered in a video just like this one, visit my TechHelp page. There's all my other cool stuff. Thanks for watching. I hope you learned something, and we'll see you next time. QuizQ1. What is the main difference between short text and long text fields in Microsoft Access?A. Short text fields store up to 255 characters, long text fields can store much more. B. Short text fields are numeric, long text fields are alphanumeric. C. Long text fields are used for phone numbers, short text fields for notes. D. Short text fields can only be used in tables, long text in forms. Q2. Which of the following data types is most appropriate for storing a customer's comment or notes? A. Currency B. Short Text C. Yes/No D. Long Text Q3. Why are phone numbers typically stored as Short Text fields in Access? A. Because they require calculations B. Because they are always more than 255 characters C. Because they may contain non-numeric characters and do not require calculations D. Because they are case-sensitive Q4. Which is NOT a drawback of using long text fields in Access? A. They can take up more space in the database B. They are prone to data truncation and corruption C. They can be used in combo boxes and list boxes by default D. They are less efficient than short text fields Q5. What happens if you press the Enter key in a short text field on a form by default? A. It adds a new line to the field B. It goes to the next field C. It saves the record D. It deletes the entry Q6. What is a unique feature of long text fields called "append only"? A. It allows changing existing data only B. It allows users to only add text to the end of the field without changing existing content C. It restricts the field to 255 characters D. It makes the field read-only Q7. Which type of text field allows storing formatting such as bold and color? A. Short Text B. Long Text C. Both Short and Long Text D. Neither Short nor Long Text Q8. What is recommended if you anticipate storing notes about a customer over time? A. Store all notes in one long text field in the customer table B. Use a separate related table to store each note as a separate record C. Use short text fields only D. Do not store notes in the database Q9. Which of the following operations are likely to cause problems if performed on long text fields? A. Group by and aggregate queries B. Simple data entry C. Plain text search within field D. Filter records by ID Q10. How much data can technologically be stored in a long text field via code in Microsoft Access? A. 64 characters B. 255 characters C. Up to 1 gigabyte D. Up to 10 megabytes Q11. Why is it NOT recommended to use long text fields for data that needs to be searched, grouped, or reported on extensively? A. Because they are indexed by default B. Because Access handles large fields poorly and may truncate data C. Because only numbers can be stored D. Because they can be used in lists and combo boxes Q12. Has the capability to sort and filter on long text fields changed in recent versions of Access? A. No, you still cannot sort or filter on long text fields B. Yes, you can now sort, search, and filter on long text fields C. You can only filter, not sort D. You can only sort, not filter Q13. If you import data into a long text field using code and then edit it in a form, what is a possible risk? A. The data will be automatically encrypted B. The data may be truncated down to 64K C. The form will crash D. The data will be converted to short text Q14. What is suggested for backups with regard to preventing data loss from corruption or truncation? A. Only keep yesterday's backup B. Keep multiple, versioned backups going back weeks, months, and years C. Backups are unnecessary with short text fields D. Only keep one backup at a time Q15. Why should you avoid storing extremely large documents like Word files in a long text field? A. They are immediately deleted by Access B. It is illegal to do so C. Large amounts of data increase risk of corruption and slow down the database D. Access can only store images, not text Q16. Which of the following is true about using long text fields in combo boxes or list boxes? A. Long text fields can be used without issue B. Long text fields cannot be used in combo or list boxes C. Both short and long text fields are equally supported D. Combo boxes and list boxes do not support text fields Q17. In Access, if you have a two-character state abbreviation field, what is the best way to restrict its size? A. Use a long text field B. Use a short text field and set the field size to 2 C. Use a currency field D. Use a yes/no field Q18. According to Richard, what is the major cause of corruption he has experienced in Access databases? A. Too many currency fields B. Storing large amounts of data in long text fields C. Too many short text fields D. Lots of forms and reports Answers: 1-A; 2-D; 3-C; 4-C; 5-B; 6-B; 7-B; 8-B; 9-A; 10-C; 11-B; 12-B; 13-B; 14-B; 15-C; 16-B; 17-B; 18-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on understanding the differences between short text and long text data types in Microsoft Access, and when to use each one.The question comes from Brandon, who asks about the differences between short text and long text fields, and when it is appropriate to use each type. Generally, if you are working with a text field in Access, short text is the best option unless you are certain the content will exceed 255 characters. For most information such as names, addresses, and phone numbers, short text is ideal. The primary exception is for comments or notes, where long text fields may be appropriate. Long text fields, which used to be called memo fields, come with several disadvantages and are less efficient for storage and performance. It's important to select the proper data type for each field in your database, just as you would for currency, date/time, or yes/no values. Short text fields can store up to 255 characters and are suitable for small bits of information such as first and last names, addresses, and phone numbers. Phone numbers, for example, should always be stored as text, since you are not performing calculations with them. Only numerical data meant for calculations should go in number fields. Long text fields, in contrast, can technically store up to about 64,000 characters (or 64 kilobytes), and even more if you use programming, but the practical limit for most users is 64K. Access theoretically allows up to one gigabyte of data to be entered in long text fields via the user interface, but this is not recommended. Instead, use long text only for data that exceeds the 255-character limit, such as notes, directions, or recipes. If you find you need to store extremely large amounts of data, consider using an external file, because Access database files are themselves limited to 2GB in total size. A benefit of short text fields is that you can easily limit their maximum size, for example to restrict state abbreviations to two characters. Long text fields do not allow such size limits. Short text stores only plain text, while long text can handle rich text, enabling formatting such as bold, italics, and various colors. On forms, short text typically appears as a single-line text box, whereas long text appears as a multi-line text box with a scroll bar; these defaults can be modified. The Enter key behaves differently in these fields by default: in short text fields, pressing Enter moves to the next field, while in long text fields, it creates a new line. These behaviors can be customized at the form level. Long text fields also have an "append only" option, which prevents users from editing existing text and allows only new text to be appended. I rarely use this feature, as it is typically better to store successive notes in related records in a separate table. When it comes to interface elements like combo boxes and list boxes, only short text fields can be used. Long text fields are not supported in these controls. Short text fields are also much more efficient when it comes to storage. For example, storing a million records with 255 characters in each, short text fields resulted in a database size of around 273 MB. The same data stored in long text fields increased the size to about 316 MB, which is substantially larger for the same contents. For this reason, it is best to use long text fields only when necessary. If database size is a concern, keep in mind that larger databases run more slowly. Also, extensive use of long text fields can increase your risk of data corruption. Long text fields are more prone to issues such as data truncation and corruption, and I have seen situations where text gets cut off unexpectedly without warning or error messages. Because of these risks, it is essential to maintain regular backups of your database. Not only should you have daily backups, but it is also wise to keep weekly, monthly, and yearly versions. This prevents the loss of important information if data corruption or truncation goes unnoticed for an extended period of time. Another pitfall with long text fields is their handling of large amounts of data imported with code. If you import significantly more than 64K into a long text field, when you later edit it in Access, anything over 64K will be truncated. This is another reason to avoid using long text for anything other than moderate notes or comments. Using long text fields in aggregate queries or certain types of SQL statements, such as union queries or group by queries, can also result in truncation or corruption. For example, using long text fields in these situations will often result in only a partial value (sometimes just the first 255 characters). This is another reason long text fields should be reserved for simple notes that are read by humans, not processed or analyzed by the database. There have been improvements in long text field handling since their days as memo fields. In older versions of Access, memo fields could not be searched, sorted, or indexed, and could not be used in aggregate queries. Newer versions now allow for searching, sorting, and even filtering on long text fields. Indexing is now possible, too, though there is rarely a practical reason to index long text fields. Some sources note that certain queries and filters might still only operate on the first 255 characters of a long text field, although I have not verified this myself. To sum up, for almost all cases, I recommend using short text fields except where you absolutely need more than 255 characters, which is often just for comments or notes. If you regularly add notes about a contact or customer, do not store all interactions in one long text field. Instead, create a related table and store each note as its own record, along with the date, time, and other relevant details. This keeps the data manageable and avoids performance issues. The statistics I quoted earlier come from a database I designed specifically to measure the efficiency differences between short and long text fields. I recorded the process, which took about three hours to build and test this database, and that video will be released for members soon. In this experiment, I set up databases to add and analyze records of various sizes, and handled tasks like compacting the database with VBA code, reading and writing text files, and recording results. It was quite a project, but provided useful insights. I will also be publishing a free TechHelp video on compacting and repairing Access databases, with further details for those interested. TechHelp videos like this will always be free to watch, and if you enjoyed this lesson, consider sharing it and subscribing for future updates. Do not forget to check out the free Access Level 1 course available on my website and on YouTube. If you would like to have your own question answered in one of these videos, be sure to visit my TechHelp page for the details. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListDifferences between short text and long text fieldsWhen to use short text vs long text in Access Character limits for short text and long text fields Field size limitations for short text Rich text formatting in long text fields Default form control types for short and long text Enter key behavior in short and long text controls Append only property of long text fields Using short text fields in combo and list boxes Database storage efficiency of short vs long text Risks of data corruption with long text fields Data truncation issues in long text fields Backup strategies for Access databases Problems importing large data into long text fields Limitations of long text fields in aggregate queries Searching, sorting, and filtering long text fields in Access Indexing capabilities of long text fields Best practices for storing multiple notes per record Using related tables for contact notes with long text Summary recommendations for text field types |
||||
|
| |||
| Keywords: TechHelp Access short text v long text memo short v long short vs long PermaLink Short Text v. Long Text in Microsoft Access |
