Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Learn about Short Text and Long Text Data Types and how to change the Data Type of a field.
Download this lesson’s related exercise files.Services_13_start.accdb
1.2 MB The Constitution of the United States.docx
26.4 KB Help_InputMaskCharacters.docx
52.7 KB Services_13__TextDataTypes.accdb
Short Text and Long Text Data Types
Allow Zero Length
When defining Short Text and Long Text fields, set Allow Zero Length to No if you do not want to allow zero-length strings (ZLS). If you are importing data from another system that could possibly have ZLS values, it is best to set this to Yes. ZLS values, however, look like nothing is there.
In a Short Text Field, the field Size property specified the maximum number of characters that can be entered.
An Input Mask allows you to specify a pattern when entering data into a field. The Input Mask then supplies placeholder characters automatically when you create or change data. Data that has already been entered will not be affected. It is best to store the Input Mask in the field so that when you export information, it will still look the same.
Long Text, which used to be called Memo, allows 65,535 characters when entering data through the user interface. If entering data programmatically, a Long Text field can store 2 gb, which is the limit for the size of an Access database. Technically, the limit would be 2 gb minus the space needed for the system objects.
Space for Long Text is allocated like pieces of paper. Even though you may write just one sentence, a whole piece of paper is used. If you fill up a page and need more, you can get another piece of paper.
Long Text is not stored in the table with other fields. A pointer to the data is stored and the actual values are stored somewhere else. As such, long text fields can create problems for other values on the record. In my opinion, it is best to define long text fields in a related table to minimize the chances of corruption.
To make a new line in a text field, press Ctrl-Enter.
When changes have been made to a record that have not been saved, a pencil icon appears in the record selector box. Click on the pencil icon to save the record or press Ctrl-S. Records are automatically saved when you move away from them.
To resize a column, drag the right border in the column header.
To resize a row, drag the bottom border in the record selector box.
If you want to be able store formatting such as bold (Ctrl-B), italic (Ctrl-I), and underline (Ctrl-U) in a long text field, set the Text Format property to Rich Text. The default value is Plain Text.
Values in a short text field can range from 0 to 255 characters.
When defining a Short Text field, always consider the number of characters that are needed and set the Field Size property accordingly.
Text fields can hold letters, numbers, and special characters.
If you have numbers that don't need to be calculated, such as phone numbers and account numbers, choose Text for the data type.
To enter today's date, press Ctrl-;
Unhide Columns Dialog Box
To get a list of checkboxes to hide or show each column, right-click on any column header in Datasheet View and choose Unhide Columns from the shortcut menu.
When a text value has no characters, it is called a ZLS, or Zero-Length String. ZLS values must be assigned as a default or programmatically as it is impossible to enter them manually.
Change Data Type of a Field
- To change the Data Type of a field, go to the Design View of a Table
- From the Data Type drop-down for the desired field, choose the main data type.
- If the Data Type is Short Text, then change the Field Size property in the lower pane.
- If the Data Type is Long Text, you may set the Text Format property in the lower pane to Rich Text to store bold, italics, underline, and other basic formatting.
- If the field is part of a defined relationship, the relationship will have to be deleted before the data type can be changed.
Delete a Relationship
- To delete a relationship, go to the Relationships Window
- Right-click on the relationship line
- Choose Delete from the shortcut menu.
Lesson notes are only available for subscribers.