• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 4. Working with Access Databases... > Choosing Field Data Types, Sizes, an...

Choosing Field Data Types, Sizes, and Formats

You must assign a field data type to each field of a table, unless you want to use the Text data type that Access assigns by default. One principle of relational database design is that all the data in a single field consists of one data type. Access provides a much wider variety of data types and formats from which to choose than most PC database managers. In addition to setting the data type, you can set other field properties that determine the format, size, and other characteristics of the data that affect its appearance and the accuracy with which numerical values are stored. Table 4.1 lists the field data types that you can select for data contained in Access tables.

Table 4.1. Field Data Types Available in Access
InformationData TypeDescription of Data Type
CharactersTextText fields are most common, so Access assigns Text as the default data type. A Text field can contain as many as 255 characters, and you can designate a maximum length less than or equal to 255. Access assigns a default length of 50 characters. A fixed-length Text data type is similar to xBase's Character field and Paradox's Alphanumeric field.
 MemoMemo fields can contain as many as 64,000 characters. You use them to provide descriptive comments. Memo fields are similar to those of xBase, except that the Memo field's data is included in the table rather than in a separate file. Access displays the contents of Memo fields in Datasheet view. A Memo field cannot be a key field, and you cannot index a Memo field.
Numeric ValuesNumberA variety of numeric data subtypes are available. You choose the appropriate data subtype by selecting one of the Field Size property settings listed in Table 4.2. You specify how to display the number by setting its Format property to one of the formats listed in Table 4.4.
 AutoNumberAn AutoNumber field is a numeric (Long Integer) value that Access automatically fills in for each new record that you add to a table. Access can increment the Auto-Number field by one for each new record, or fill in the field with a randomly generated number, depending on the New Values property setting that you choose. An AutoNumber field creates a value similar to xBase's and Paradox's record number. The maximum number of records in a table that can use the AutoNumber field is slightly more than two billion.
 Yes/NoLogical (Boolean) fields in Access use numeric values: (Logical Fields) −1 for Yes (True) and 0 for No (False). You use the Format property to display Yes/No fields as Yes or No, True or False, On or Off, or −1 or 0. (You also can use any non-zero number to represent True.) Logical fields cannot be key fields but can be indexed.
 CurrencyCurrency is a special fixed format with four decimal places designed to prevent rounding errors that would affect accounting operations where the value must match to the penny (similar to the Paradox Currency data type).
Dates and TimesDate/TimeDates and times are stored in a special fixed format. The date is represented by the whole number portion of the Date/Time value, and the time is represented by its decimal fraction. You control how Access displays dates by selecting one of the Date/Time Format properties listed in Table 4.4.
Large ObjectsOLE ObjectIncludes bitmapped graphics, vector-type drawings, (BLOBs, binary waveform audio files, and other data types that can be large objects) created by an ActiveX component application (formerly known as an OLE server application), some of which are listed in Table 4.3. You cannot assign an OLE Object as a key field, nor can you include an OLE Object field in an index.
Web and otherHyperlinkHyperlink fields store Web page addresses. A Web address HTML document stored in the Hyperlink field may refer to a Web page onaddresses the Internet or stored locally on your computer or net-work. Clicking a Hyperlink field causes Access to start your Web browser and display the referenced Web page; choose Insert, Hyperlink to add a new hyperlink address.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint