Home

Introduction to Data Types

Overview of Data Types

Introduction 

A data type represents the kind of information that a particular field should or must hold. Every field in your database objects (tables, forms, and reports) should have the right type and it is your role to select the most appropriate of them. This helps both you and the user who performs data entry. Furthermore, a good design will cause you less headache when

creating calculated expressions. Imagine trying to multiply a FirstName by a DateHired. As much as you can, you should make sure that the right kind of data is typed in the right field.

Microsoft Access helps you allow or exclude categories of data in database fields. Using this, you can make sure that the user would not type a contract's date in a project's contact name. To manage different forms of information you enter in a database, Microsoft Access helps you organize data by categories.

Techniques of Controlling Data Entry Properties

To make your database efficient, in some circumstances, or depending on the project (or customer), you should exercise as much control as possible on data entry. Without considering that data entry people are not intelligent enough, it is your job to help or guide them by allowing the right type of information in a field. This control is mostly exercised at two levels: tables and forms.

To control data entry at the table level, a database environment like Microsoft Access provides data types. To specify a data type for a field on a table, you must open the table in Design View and select a data type under the Data Type column for the corresponding column. Because the list of data types may appear short to you, each data type provides some options that allow you to configure how data in that column either would be selectively entered or would display. To do this, after selecting a data type in the Data Type combo box, in the lower section of the table, you can format or further configure the column.

After a field has been configured on a table, when that field is used in a form, it would respect the formatting that was done on the table. Even if you create an unbound field on a form, you can still control how it accepts or rejects data. Therefore, data entry can also be configured at the form level. To provide this functionality, a form provides the same properties as the lower portion of the table.

To further control how data is entered and/or how it is displayed in a field, both the table in Design View and the Properties window in Form Design provide special characteristics.

The lower part of the table Design View is made of two sections: the property pages on the lower left and the properties help section on the lower right:

The kind of Data Type you set for a column in the upper section controls what displays in the lower section of the view. The General property page controls regular features common to the selected data type:

Field Size: The Field Size property is available for a data type and since data types are specified only on a table, it is available only on a table. The Field Size depends on the type of data selected but it is available only for text and numbers.

Format: The Format property is used to specify how the value(s) for a column should display.

The Lookup tab allows you to specify a feature that is particular with a specific data type of the upper section of the field.

Once you have selected a data type for a certain field in the upper section of the view, that data type will provide its own sub-categories. To display the sub-categories of a data type, select that data type in the upper section of the view.

 
 

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:

Character Used to enter or accept
0 A single digit
9 A single digit or space
# A digit, space, + or –
L An alphabetical character
? A letter
A A letter or a digit
a A letter, a digit, or nothing
& A character or space
C A character, space, or nothing
. A decimal place holder; for US English, this would be the period
, Thousand separator; for US English, this would be a comma
:;-/ Date and time separator, as specified in the Regional Settings of Control Panel
< A letter; the letter will be converted to lowercase
> A letter; the letter will be converted to uppercase
! Anything; the mask is filled from right to left for this position
| Anything; the character that follows this one will be displayed itself. For example, if you type |L, the letter L would be displayed instead of being used as a 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:

Mask Example 1 Example 2 Example 3
LL dh fT Gm
>LL\-00 WE-47    
#0L 8f 16a 04t
>LL\-000 WE-883    
000\-000\-0000 265-387-6498    
##\-## 02-37 -9-5+ -6-35
&#\-L0\-## 5-u5-00    
\(000") "000\-0000 (301) 294-6464    
!\(999") "000\-0000"" ( ) 392-3873    
00\-00\-00\-00 28-73-68-46    
>00\-LLLL\-0 78-DRUG-9    
>L<LLL Jean    
>L<????????????? Helene Antananarivo  
 

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

S1 Determine appropriate data inputs/outputs for your database
S12 Use multiple data types
S15 Use the Input Mask Wizard
 

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
 

Previous Copyright © 2002-2007 FunctionX, Inc. Next