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

Chapter 5. Working with Jet Databases an... > 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 Jet assigns by default. One principle of relational database design is that all the data in a single field consists of one data type. Jet 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 5.1 describes the field data types that you can select for data contained in Jet tables.

Table 5.1. Field Data Types Available in Jet 4.0
Data Type Description of Data Type
Text Text 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.
Memo Memo fields ordinarily can contain as many as 65,535 characters. You use them to provide descriptive comments. Access displays the contents of Memo fields in Datasheet view. A Memo field can't be a key field.
Numeric Values
Number Several numeric data subtypes are available. You choose the appropriate data subtype by selecting one of the Field Size property settings listed in Table 5.2. You specify how to display the number by setting its Format property to one of the formats listed in Table 5.3.
AutoNumber An AutoNumber field is a numeric (Long Integer) value that Jet automatically fills in for each new record you add to a table. Jet can increment the AutoNumber field by 1 for each new record, or fill in the field with a randomly generated number, depending on the New Values property setting that you choose. The maximum number of records in a table that can use the AutoNumber field with the Long Integer size is slightly more than two billion.
Yes/No Logical (Boolean) fields in Access use numeric values: –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 can also use any nonzero number to represent True.) Logical fields can't be key fields but can be indexed .
Currency Currency is a special fixed format with four decimal places designed to prevent rounding errors that would affect accounting operations in which the value must match to the penny.
Dates and Times
Date/Time Dates 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 5.3.
Large Objects
OLE Object Includes bitmapped and vector-type graphics, and other BLOBs (binary large (Binary Data) objects), such as waveform audio files and video files. You can't assign an OLE Object field as a key field, nor can you include an OLE Object field in an index. Clicking an OLE Object in Datasheet view opens the object in its editing application.
Web Addresses
Hyperlink Hyperlink fields store Web page document addresses. A Web address stored in the Hyperlink field can refer to a Web page on the Internet or one stored locally on your computer or network. Clicking a Hyperlink field in datasheet view causes Access to start your Web browser and display the referenced Web page. Choose Insert, Hyperlink to add a new hyperlink address to a Hyperlink field. Hyperlink is an Access, not a Jet, data type.
Related Data
Lookup Wizard Lookup Wizard isn't a legitimate data type; it's a property of a field. Selecting Lookup Wizard starts the Lookup Wizard to add a lookup feature to the table. Most lookup operations execute a query to obtain data from a field of a related table.



Not a subscriber?

Start A Free Trial

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