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 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 other 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.
Note The OLE Object field data type is unique to Access; other applications that use Jet designate the OLE Object field data type as Binary or Long Binary. When you add an OLE object, such as a bit-mapped graphic from Windows Paint or a Word document, Access adds a special header, which identifies the source application, to the binary graphics data. Other applications can't read data from OLE Object fields you create in Access. OLE Object fields won't upsize to SQL Server 2000, because SQL server's image fields don't support the OLE Object data type. Choosing Field Sizes for Numeric and Text DataThe Field Size property of a field determines which data type a Number field uses or how many characters fixed-length text fields can accept. Field Size properties are called subtypes to distinguish them from the data types listed in Table 5.1. For numbers, you select a Field Size property value from the Field Size drop-down list in the Table Design window's Field Properties pane (see Figure 5.6). Figure 5.6. You can select one of seven Field Size (data subtype) property values for fields having a Number data type from the drop-down list. Subtypes for Numeric DataThe Number data type of the previously shown Table 5.1 isn't a fully specified data type. You must select one of the subtypes from those listed in Table 5.2 for the Field Size property to define the numeric data type properly. To select a data subtype for a Number field, follow these steps:
After you select a Field Size property, you select a Format property from those listed in Table 5.3 (later in this chapter) to determine how to display the data. Table 5.2 includes the Currency data type because it also can be considered a subtype of the Number data type. Regardless of how you format your data for display, the number of decimal digits, the range, and the storage requirement remains that specified by the Field Size property. Note These data types are available in Visual Basic for Applications 6.0. VBA includes all the data types listed in Table 5.2 as reserved words. You can't use a reserved data type word for any purpose in VBA functions and procedures other than to specify a data type.
As a rule, you select the Field Size property that results in the smallest number of bytes that encompasses the range of values you expect and that expresses the value in sufficient precision for your needs. Mathematical operations with Integer and Long Integer proceed more quickly than those with Single and Double data types (called floating-point numbers) or the Currency and Date/Time data types (fixed-point numbers). Microsoft added the Decimal data subtype for conformance with the SQL Server decimal data type. Tip Always use the Decimal data type for fractional values such as percentages that you intend to use for calculating Currency or other Decimal values. The Order Details table's Discount field uses the Jet Single data type, which is notorious for causing rounding errors in decimal calculations. Note You can apply the Replication ID field size to Number or AutoNumber fields. A replication ID is a specially formatted 32-character (16-byte) hexadecimal number (values 0 through 9 and A through F) surrounded by French braces. The more common name for a replication ID is globally unique identifier (GUID, pronounced "goo id" or "gwid"). A typical GUID looks like {8AA5F467-3AF5-4669-B4CB-5207CDC79EF4}. GUID values, which Windows calculates for you, supposedly are unique throughout the world. If you apply the Replication ID field size to an AutoNumber field, Access automatically adds a GUID value for each row of the table. Fixed-Width Text FieldsYou can create a fixed-width Text field by setting the value of the Field Size property. By default, Access creates a 50-character-wide Text field. Enter the number, from 1 to 255, in the Field Size cell corresponding to the fixed length that you want. If the data you import to the field is longer than the selected field size, Access truncates the data; so, you lose the far right characters that exceed your specified limit. You should enter a field length value that accommodates the maximum number of characters that you expect to enter in the field. Note The terms fixed-width and fixed-length have two different meanings in Access. Even if you specify a fixed-width for a field of the Text field data type, Access stores the data in the field in variable-length format. Thus, setting the Length value to 255 for all Text fields has no effect on the ultimate size of the database file. Selecting a Display FormatYou establish the Format property for the data types that you select so that Access displays them appropriately for your application. You select a format by selecting the field and then clicking the Format text box in the Field Properties window. Figure 5.7 shows the choices that Access offers for formatting the Long Integer data type. You format Number, Date/Time, and Yes/No data types by selecting a standard format or creating your own custom format. The following sections describe these two methods. Figure 5.7. You can apply one of seven numeric display formats to fields of the Number Data type and the Long Integer subtype. Access 2000 added the Euro format. Standard Formats for Number, Date/Time, and Yes/No Data TypesAccess provides 18 standard formats that apply to the numeric values in fields of the Number, Date/Time, and Yes/No data types. Format is an Access, not a Jet, field property. The standard formats shown in Table 5.3 should meet most of your needs.
Microsoft's Year 2000 (Y2K) compliance features include an Access 2000 addition to the General page of the Options dialog (Tools menu). The Use Four-Digit Year Formatting frame has two check boxes: This Database and All Databases. Marking either check box changes Date/Time field formatting as shown in Table 5.4. Long Date and Time formats don't change; the formatting shown in the Access 2003 Default column is based on the standard Windows Short Date format, m/d/yy.
Marking the This Database check box sets a flag in the current database, so the formatting changes apply only to the current database. Marking the All Databases check box adds a Registry entry to your PC, so opening any Jet database in Access forces four-digit year formatting. Tip Access's Short Date (m/d/yy and mm/dd/yy) formats for the English (United States) locale default to two-digit years unless you change the default date format of Windows or set the Four-Digit Year Formatting option(s). Two-digit year presentation isn't Y2K compliant. To make the Windows short date format Y2K compliant for most applications, open Control Panel's Regional and Language Options tool, click the Customize button and the Date tab, and check the Short Date setting. If the Short Date style is M/d/yy, change it to M/d/yyyy. The Null Value in Jet TablesFields in Jet tables can have a special value, Null, which is a new term for most spreadsheet users. The Null value indicates that the field contains no data at all. Null is similar but not equivalent to an empty string (a string of zero length, "", often called a null string). For now, the best synonym for Null is no entry or unknown. The Null value is useful for determining whether a value has been entered in a field, especially a numeric field in which zero values are valid. The next section and the later "Setting Default Values of Fields" section use the Null value. Custom Display FormatsTo display a format that's not a standard format in Access, you must create a custom format. You can set a custom display format for any field type, except OLE Object, by creating an image of the format with combinations of a special set of characters called placeholders (see Table 5.5). Figure 5.8 shows an example of a custom format for date and time. If you type mmmm dd, yyyy - hh:nn as the format, the date 03/01/01 displays as March 1, 2001 - 00:00. Access automatically adds double quotes around the comma when you save the table. Figure 5.8. If one of the standard Format property values doesn't meet your needs, you can type a string to represent a custom format in the Format text box. Except as noted, the sample numeric value that Table 5.4 uses is 1234.5. Bold type distinguishes the placeholders that you type from the surrounding text. The resulting display is shown in monospace type.
The Format property is one of the few examples in Access in which you can select from a list of options or type your own entry. Format uses a true drop-down combo list; lists that enable you to select only from the listed options are drop-down lists. The comma is a nonstandard formatting symbol for dates (but is standard for number fields). When you create nonstandard formatting characters in the Field Properties window, Access automatically encloses them in double quotation marks.
Figure 5.9. Changing from Design to Datasheet view after making changes to the table's design displays the upper message box. If you close the table in Design view, the lower message box gives you the option of saving or discarding changes, or returning to Table Design view. If you apply the custom format string mmmm dd", "yyyy (refer to Figure 5.8) to the BirthDate field of the Employees table, the BirthDate field entries appear as shown in Figure 5.10. For example, Nancy Davolio's birth date appears as December 08, 1968. The original format of the BirthDate field was dd-mmm-yyyy, the format also used for the HireDate field. The BirthDate caption property value appears in the heading row. Figure 5.10. The BirthDate field of the modified employees table displays the effect of applying mmmm dd", "yyyy as the custom date/time format. You must expand the width of the BirthDate field to accommodate the additional characters in the Long Date format. You increase the field's width by dragging the field name header's right vertical bar to the right to display the entire field. Access displays the time of birth as 00:00 because the decimal fraction that determines time is 0 for all entries in the BirthDate field. The following is an example that formats negative numbers enclosed in parentheses and replaces a Null entry with text: $###,###,##0.00;$(###,###,##0.00);0.00;"No Entry Here" The entries 1234567.89, 1234567.89, 0, and a Null default value appear as follows: $1,234,567.89 $(1,234,567.89) 0.00 No Entry Here Using Input MasksAccess 2003 lets you restrict entries in Text fields to numbers or to otherwise control the formatting of entered data. Access 2003's Input Mask property is used to format telephone numbers, Social Security numbers, ZIP codes, and similar data. Table 5.6 lists the placeholders that you can use in character strings for input masks in fields of the Text field data type.
For example, typing \(000") "000\-0000 as the value of the Input Mask property results in the appearance of (___) ___-____ for a blank telephone number cell of a table. Typing 000\-00\-0000 creates a mask for Social Security numbers, ___-__-____. When you type the telephone number or Social Security number, the digits that you type replace the underscores. Note The \ characters (often called escape characters) that precede parentheses and hyphens specify that the character that follows is a literal, not a formatting character. If the format includes spaces, enclose the spaces and adjacent literal characters in double quotation marks, as shown for the telephone number format.
Figure 5.11. The Input Mask Wizard lets you select one of 10 preset formats to specify a fixed data entry pattern for the selected field. In the second wizard dialog, you can add a custom format. |
To learn how to use the Lookup Wizard, 
When you change Format or any other property field, and then change to Datasheet view to view the result of your work, you must first save the updated table design. The confirmation dialog shown at the top of Figure 5.9 asks you to confirm any design changes. Clicking No returns you to Table Design view. If you want to discard your changes, close Table Design view and click No when asked if you want to save your changes (see Figure 5.9, bottom).
Access includes an Input Mask Wizard that opens when you move to the Input Mask field for the Text or Date/Time field data type and click the Builder (...) button at the extreme right of the text box. Figure 5.11 shows the opening dialog of the Input Mask Wizard, which lets you select from 10 common input mask formats.
EAN: 2147483647
Pages: 417