Part 2 of Keeping your information accurate in Access 2003 and Access 2002: Using the data type to restrict data

The information covered in this article is provided by the Microsoft Press. For more information, visit the following Microsoft Web site: This article is part 2 of a series of eight articles that explain how to keep your information in Access accurate. To view the other articles in this series, see the "Additional resources" section later in this article.

This information is an excerpt from chapter 6: "Keeping Your Information Accurate" of the book Microsoft Access Version 2002 Step by Step. For more information about this book, see the "References" section.

The Data Type setting restricts entries to a specific type of data: text, numbers, dates, and so on. If, for example, the data type is set to Number and you attempt to enter text, Access refuses the entry and displays a warning.

In this exercise, you will create a brand new database, you will add fields of the most common data types, and then you'll experiment to see how the Data Type setting and Field Size property can be used to restrict the data entered into a table. Follow these steps:

1. In the New File task pane, click Blank Database in the New section to display the File New Database dialog box.
   
   If the New File task pane is not displayed, click the New button on the toolbar.
2. Type Field Test in the File name box, and then click Create.
   
   Access opens the database window for the new database.
3. Double-click Create table in Design view.
   
   A blank Table window opens in Design view so that you can define the fields that categorize the information in the table. You will define five fields, one for each of the Text, Number, Date/Time, Currency, and Yes/No data types.
4. Click in the first Field Name cell, type TextField, and press TAB to move to the Data Type cell.
5. The data type defaults to Text, which is the type you want. So press TAB twice to accept the default data type and move the insertion point to the next row.
6. Type NumberField, and press TAB to move to the Data Type cell.
7. Click the down arrow to expand the list of data types, click Number, and then press TAB twice.
8. Repeat steps 4 through 7 to add the following fields:

      Field                   Data Type
      ---------------------------------
      Date Field              Date/Time 
      Currency Field          Currency 
      Boolean Field           Yes/No

   TIP: The data type referred to as Yes/No in Access is more commonly called Boolean (in honor of George Boole, an early mathematician and logistician). This data type can hold either of two mutually exclusive values, often expressed as yes/no, 1/0, on/off, or true/false.
9. Click the Save button, type Field Property Test to name the table, and then click OK.
   
   Access displays a dialog box recommending that you create a primary key.
10. You don't need a primary key for this exercise, so click No.
11. Click the row selector for TextField to select the first row.
    
    Your table now looks like the one shown here:
    
    
    
    
    The properties for the selected field are displayed in the lower portion of the dialog box.
12. Click in each field and review its properties, and then click the View button to display the table in Datasheet view, as shown here:
    
    
13. The insertion point should be in the first field. Type This entry is 32 characters long, and press TAB to move to the next field.
14. Type Five hundred, and press TAB.
    
    The data type for this field is Number. Access displays an alert box refusing your text entry.
15. Click OK, replace the text with the number 500, and press TAB.
16. Type a number or text (anything but a date) in the date field, and press TAB. When Access refuses it, click OK, type Jan 1, and press TAB.
    
    The date field accepts almost any entry that can be recognized as a date, and displays it in the default date format. Depending on the format on your computer, Jan 1 might be displayed as 1/1/2001 or 1/1/01.
    
    TIP: If you enter a month and day but no year in a date field, Access assumes the date is in the current year. If you enter a month, day, and two-digit year from 00 through 29, Access assumes the year is 2000 through 2029. If you enter a two-digit year that is greater than 29, Access assumes you mean 1930 through 1999.
17. Type any text or a date in the currency field, and press TAB. When Access refuses the entry, click OK, type -45.3456 in the field, and press TAB.
    
    Access stores the number you entered but displays ($45.35), the default format for displaying negative currency numbers.
    
    TIP: Access uses the regional settings in Microsoft Windows Control Panel to determine the display format for date, time, currency, and other numbers. If you intend to share database files with people in other countries, you might want to create custom formats to ensure that the correct currency symbol is always displayed with your values. Otherwise, the numbers won't change, but displaying them as dollars, pounds, marks, or lira will radically alter their value.
18. Try entering text or a number in the Boolean field. Then click anywhere in the field to toggle the check box between Yes (checked) and No (not checked), finishing with the field in the checked state.
    
    This field won't accept anything you type; it only allows you to switch between two predefined values. Your datasheet now resembles the one shown here:
    
    
    
    
    TIP: In Design view, you can use properties on the Lookup tab to display the Boolean field as a check box, text box, or combo box. You can also set the Format property on the General tab to use True/False, Yes/No, or On/Off as the displayed values in this field (though the stored values will always be -1 and 0).
19. Save and close the table, and then close the database.

Additional resources

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

The information in this article is an excerpt from the Microsoft Access Version 2002 Step by Step book, published by Microsoft Press. To learn more about the Microsoft Access Version 2002 Step by Step book, and to see a sample chapter from this book, visit the following Microsoft Web site: For more information about this publication and about other Microsoft Press titles, visit the following Microsoft Web site: