• 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.

MemoMemo 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 cannot be a key field.
Numeric ValuesNumberVarious 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.3.
 AutoNumberAn AutoNumber field is a numeric (Long Integer) value that Access automatically fills in for each new record you add to a table. Access 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 is slightly more than 2 billion.
 Yes/NoLogical (Boolean) fields in Access use (Logicalnumeric values: –1 for Yes (True) and fields) 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 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.
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.3.
Large ObjectsOLE ObjectIncludes bitmapped graphics, vector-type (BLOBs, drawings, waveform audio files, and other binary data types that can be created by an large ActiveX component application objects). 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 HTML document addresses. A Web address stored in addresses the Hyperlink field may refer to a Web page on the Internet or one stored locally on your computer or network. 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