Introduction to Data Types

Strings and Data Types

Introduction

We have defined a string as an empty text, a letter, a word or a group of words considered “as is”. This type of data is created on a table by selecting the Text data type. A Text data type allows the user to type any kind of characters or group of characters. This field can hold up to 255 characters.

Practical Learning: Setting Appropriate Data Types

1. From the resources that accompany this book, copy the Rockville Techno1 database to your Exercises folder and then open the Rockville Techno1 database
2. In the Database window, click the Tables button if necessary
   From the Tables section, open the Employees table and switch it to Design View
3. Notice that the default Data Type of most fields is Text

Field Size

The size of a string is usually considered as the number of characters it contains or may hold. When creating a field that would hold text, the default size provided is 50 characters, as the number 50 is set in the Field Size property. You can therefore decrease or increase this size for any number between 1 and 255. The default value, 50, is enough in most cases, such as providing a (short) description for a product.

When setting the Field Size property by itself, the database would make sure that the user can only type so many characters. For example, it is very unlikely to have a first name of a person that is 50 characters. In this case, you can safely reduce the number of characters to 20 or 30. On the other hand, if you are creating a column whose length is hard to predict, you can set the length to a reasonable value. There are other ways you can decrease the number of characters that are allowed in a field. Over all, you should be able to predict the types of values that would be entered in the fields of a column.

Practical Learning: Setting Field Sizes

1. In the upper section of the view, click FirstName
2. In the lower section of the view, click Field Size and type 20
   This will allow the user to type only 20 characters
3. In the upper section of the view, insert new row between FirstName & LastName, type MI and press F6 to move the caret to the lower section of the view
4. As the caret is on the Field Size field, type 1 
5. Set the Field Size of LastName to 20 
6. Set the Field Size of Address to 100
7. Save the table

Text Input Masks

Because users can be tempted or distracted to enter just anything in a Text field, Microsoft Access provides techniques to control what goes in a field and what must be prevented. This is the role of a mask. A mask is a technique of creating sections, also called placeholders, in a field. A section can be configured to accept only a letter, only a digit, a character or a digit, any symbol, nothing, or to display a particular symbol that the user cannot change. When creating the mask, you will use some predefined characters and create a combination of your choice.

Before creating a mask for a field, Microsoft Access comes with various masks you can apply to a field to control user's input such as dates, times, US Social Security Number, Currency values, etc. To apply one of these masks, you can use the Input Mask Wizard. To do this, first set the Data Type of the desired field to Text. Then, in the lower section of the table, click the Input Mask property and click its ellipsis button . This would start the wizard.

In the first page of the Input Mask Wizard, you can choose one of the popular provided formulas including US and Canada telephone number, Social Security Number, ZIP Code, etc:

Once you have located the desired mask, you can click it and click Next to continue. If none of the masks suits your need, you can create a new one and add it to the list. To do this, click the Edit List button. This would bring the Customize Input Mask Wizard dialog box:

After creating a new mask, click Close. Once you have located the desired mask, you can click it and click Next to continue. The second page of the wizard allows you to accept or customize the mask you had selected. Once you are ready, you can click Next. The third page presents an option that allows you to specify how the value in the field will be stored in the database. It presents two radio buttons and you can either accept the suggestion or select the other and click Next. The fourth page of the wizard does not do much. It simply lets you know that the wizard is ready to create the mask. Therefore, you can click Finish.

If none of the masks provided by the wizard suits you, you can create your own. To do that, click the Input Mask property for the desired field and use the following characters to create the mask:

You can use any of these symbols to create a mask. If you want to include a word or sentence as part of the mask, type it in any section as desired. Here are examples of Custom Masks:

Practical Learning: Using Input Masks

1. The Clarksville Ice Cream database should still be opened and displaying the Employees table in Design View
   In the upper section of the table, click EmployeeNumber
2. In the lower section, click Input Mask, type >LL-000 and press Enter
3. In the upper section, click ZIPCode and press F6. In the lower section, change the Input Mask to 00000
4. In the upper section of the table, click WorkPhone and, in the lower section, click Input Mask
5. On the right side of the Input Mask field, click the ellipsis button
    
6. In the first page of the of the Input Mask Wizard, click Phone Number (in Microsoft Access 2000, it should be selected by default) and click Next
7. In the second page, press Tab twice to position the caret in the Try It edit box. Type 0000000000 to test it
    
8. Click Next
9. In the third page, click the With The Symbols In The Mask radio button
    
10. Click Next and click Finish. Notice the new mask in the field
11. Save the table

Strings Formats

After a user has entered data in a field, without or without the influence of a mask, you can specify how the value of the field should be displayed. This is controlled by the Format property. To assist you with this configuration, the Format property depends on the type of data set for the field.

After creating a mask, if you want to prevent duplicate data, remember to specify this in the Indexed property.

Practical Learning: Setting Fields Format 

1. Open the Bethesda Car Rental1 database
2. Open the Employees table in Design View
3. In the upper section of the view, click EmailAddress
4. In the lower section of the view, click Format. Type < to make sure the content of the Email Address field will be converted to lowercase
5. In the upper section, click MI
6. In the lower section, click Format, type >
7. Click Input Mask, type L and press Enter
8. Open the Rockville Techno1 database and, from the Tables section, open the Employees table in Design View
9. In the upper section of the table, click State
10. In the lower section, click Input Mask, and type >LL and press Enter
11. In the upper section of the table, click EmployeeNumber
12. In the lower section, click Input Mask. Type >LL\-000;0;_ and press Enter

The Memo Data Type

The Memo data type is like the Text type except that it can hold a longer text up to 64000 characters. The text is mostly provided as ASCII. This means that there is no formatting.

Practical Learning: Setting Memo Types

1. Open the Rockville Techno database and, from the Tables section of the Database window, open the Employees table in Design View
2. Under the Field Name column, click Observations. Press Tab, type m and press Enter. Notice that the data type has been set to Memo
3. Save the table

MOUS Topics

Exercises 

Yugo National Bank

1. Open the Yugo National Bank database. Open the AccountTypes table in Design View and change the Data Type of the Description field to Memo. Save and close the table
2. Open the AccountTypes form in Design View. Delete the Description text box (and its accompanying label). Using the Field List, add the Description field to the form where the other Description text box was
    
   
    
   Save and close the form
3. In the Customers table, reduce the possible length of characters of the Address field to 50 characters then save and close the table
4. Reduce the possible number of characters of the Address column of the Employees table to 50 characters then save and close the table

Tenley Associates

1. Open the Tenley Associates database. Configure the EmployeeNumber field of the Employees table so that examples of possible numbers would be:
   1. 648-DL-TG or
   2. 762-7D-GG or
   3. 376-8B-BP
   That is, an employee number is made of three sections separated by a dash symbol:
   1. The first section is made of 3 digits and only digits
   2.  The first character of the second section can be a letter or a digit
   3.  The second character of the second section can and must only be a letter
   4. The third section must be made of 2 letters
2. Change the ZIPCode of the Employees table to allow only 5 digits

Watts A Loan

1. Open the Watts A Loan database. In the Customers table, reduce the Address field to allow only 65 characters then save and close the table
2. Open the Employees form and create a few employees records
3. Open the Customers form and create a few accounts