Locked lesson.

Upgrade

About this lesson

Learn about Short Text and Long Text Data Types and how to change the Data Type of a field.

Quick reference

Short Text and Long Text Data Types

Application Terminology

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.

Field Size

In a Short Text Field, the field Size property specified the maximum number of characters that can be entered.

Input Mask

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

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.

New Line

To make a new line in a text field, press Ctrl-Enter.

Pencil icon

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.

Resize Column

To resize a column, drag the right border in the column header.

Resize Row

To resize a row, drag the bottom border in the record selector box.

Rich Text

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.

Short 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.

Today's Date

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.

ZLS

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.

Steps

Change Data Type of a Field

  1. To change the Data Type of a field, go to the Design View of a Table
  2. From the Data Type drop-down for the desired field, choose the main data type.
  3. If the Data Type is Short Text, then change the Field Size property in the lower pane.
  1. 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.
     
  2. 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

  1. To delete a relationship, go to the Relationships Window
  2. Right-click on the relationship line
  3. Choose Delete from the shortcut menu.
     

      

Login to download
  • 00:05 This is Access 2013, Lesson 13.
  • 00:09 We are continuing our journey into Data Types.
  • 00:13 Hi this is Crystal.
  • 00:15 We are going to explore Short Text and Long Text,
  • 00:18 plain text and rich text, and more.
  • 00:22 Go to the Design View of the MyDataTypes table
  • 00:25 we created in the last lesson.
  • 00:28 Find a blank row.
  • 00:30 Create a field called ShTxt that is Short Text with Size of 75.
  • 00:39 I have my default Field Size for text set at 50.
  • 00:43 Next will be LngTxt. This used to be called Memo.
  • 00:51 Space is allocated in pages and
  • 00:53 what is stored in the table is just a pointer to the data.
  • 00:57 When you are going to write on a piece of paper,
  • 01:00 you take a whole piece of paper
  • 01:01 even if all you write is one line.
  • 01:05 Long Text fields allocate space like whole pages of paper --
  • 01:10 not just what you need, but usually a lot more.
  • 01:13 Then, as you fill up a page, you get another piece of paper.
  • 01:17 Having a Long Text field in a table can
  • 01:19 create problems for the other fields on the record.
  • 01:23 That is why it is advisable to store long text fields
  • 01:27 in a separate table, away from the rest of your data.
  • 01:30 If that table gets really big, you may want to put your tables
  • 01:34 with Long Text fields in their own database and link to them.
  • 01:39 Define a Long Text field called RichTxt
  • 01:42 and set its Text Format property to Rich Text.
  • 01:49 Save the Table Design and
  • 01:51 switch to Datasheet View to enter information.
  • 01:54 We don't need to see the number fields right now.
  • 01:58 We experimented with those in the last lesson.
  • 02:01 Right-click in one of the field headers
  • 02:04 and choose Unhide Fields from the shortcut menu,
  • 02:08 even though what we really want to do is hide them.
  • 02:11 Uncheck the number fields in the list
  • 02:14 and also uncheck the Click to Add column.
  • 02:18 Now we are left with an AutoNumber ID on 4 records
  • 02:22 and the text fields we just created.
  • 02:27 ShTxt is a Short Text field.
  • 02:31 It can hold letters, numbers, and special characters.
  • 02:36 The Size of this field is set to 75 characters,
  • 02:41 that would be the maximum number of characters
  • 02:44 that can be entered.
  • 02:47 The maximum number of characters for a short text field is 255.
  • 02:53 To zoom so you can see more text, press Shift-F2.
  • 02:58 In the zoom box, you can change the font.
  • 03:02 The change will be remembered as long as you are in Access.
  • 03:06 When you close and open Access again,
  • 03:09 you will have to change the zoom box font again.
  • 03:13 Long Text can hold up to 64K.
  • 03:17 That is 64 x 1024, which is about 64,000
  • 03:23 A pointer to the actual data is stored in the table.
  • 03:27 Text from the Constitution was pasted
  • 03:30 into both of the Long Text fields.
  • 03:33 In the Plain Text field, you don't see formatting.
  • 03:37 In the Rich Text field, the formatting is stored.
  • 03:42 To resize a column, drag the the column header right border.
  • 03:46 To resize a row, drag the bottom border
  • 03:49 in the record selector box.
  • 03:52 To enter today's date, press Ctrl-;
  • 03:56 To enter the current time, press Ctrl-: ( which is Ctrl-Shift-; )
  • 04:01 When you enter a date and time in the same field,
  • 04:04 use a space to separate them.
  • 04:08 Even though a date and time was entered into this field,
  • 04:13 it will be treated as text because the data type is text.
  • 04:19 In Access, data is automatically saved without you having
  • 04:24 to do anything except move off a record or close a table.
  • 04:30 When you are changing data on a record,
  • 04:33 you will see a pencil icon in the record selector area.
  • 04:37 This means that the data is not yet saved.
  • 04:40 To save the record immediately,
  • 04:42 click on the pencil icon or press Ctrl-S.
  • 04:48 When you are entering data into a rich text field,
  • 04:52 press Ctrl-B for bold, Ctrl-I for italic, and Ctrl-U for underline.
  • 04:58 There's lots more shortcut keys.
  • 05:01 Press Ctrl-Enter to make a new line.
  • 05:06 Let's add more fields. Go back to the design view.
  • 05:11 If you have a number you won't calculate,
  • 05:13 like a Phone number, choose Text for the data type.
  • 05:17 You can even specify the placeholder characters.
  • 05:20 Create a field for Phone that is Short Text with a Size of 20.
  • 05:26 Click on the InputMask property and then the Builder Button.
  • 05:31 Choose Phone from the list of choices.
  • 05:35 Store the InputMask. Accept all the other defaults.
  • 05:42 You can create your own InputMask codes.
  • 05:46 Create a field called Code.
  • 05:48 Makie it Short Text, field Size 20.
  • 05:51 click on the InputMask wizard and create your own mask.
  • 05:57 You can test it to see how it will look.
  • 06:01 Now let's save the table design and go to the Datasheet View.
  • 06:07 As you can see, the InputMask
  • 06:09 supplies the placeholder characters
  • 06:12 as you are putting your data in.
  • 06:15 In summary, we've covered Short Text and Long Text.
  • 06:22 Short Text is limited to 255 characters
  • 06:25 but if you are going to index it, or sort it then
  • 06:30 don't go above 240 characters.
  • 06:33 It should be shorter than that for indexing and sorting anyway.
  • 06:37 Long Text allocates space in pages and can be plain or rich.
  • 06:44 In the next lesson, we will change the design of this table
  • 06:48 to experiment with more data types.

Lesson notes are only available for subscribers.

Gift this course