Microsoft Access 2010 Bible

(Rick Simeone) #1

Chapter 2: Creating Access Tables


65


Several examples of custom text formats using the “English (United States)” regional settings are
presented in Table 2.5.

TABLE 2.5


Format Examples


Format Specified Data as Entered Formatted Data as Displayed
> Adam Smith ADAM SMITH
#,##0;(#,##0);0;None 15, -15, 0, No Data 15, (15), 0, None
Currency 12345.67 $12,345.67
“Acct No.” 0000 271 Acct No. 0271
mmm yy 9/17/12 Sep 12
Long Date 9/17/12 Thursday, September 17, 2012

The second example in this table requires a bit of explanation. A field’s Format property accepts a
semicolon-delimited string with as many as four elements. The first two elements are the specifiers
when the field’s value is positive or negative, respectively, while the last two are the specifications
for zero and null values. In row two of Table 2.5, the format string specifies that negative values
are to be enclosed in parentheses, while null values are represented by None.

Yes/No field formats
A Yes/No field displays Yes, No, True, False, On, or Off, depending on the value stored in
the field and the setting of the Format property for the field. Access predefines these rather obvi-
ous format specifications for the Yes/No field type:

l (^) Yes/No: Displays Yes or No
l True/False: Displays True or False
l (^) On/Off: Displays On or Off
Yes, True, and On all indicate the same “positive” value, while No, False, and Off indicate the
opposite (“negative”) value.
Access stores Yes/No data in a manner different from what you might expect. The Yes data is
stored as –1, whereas No data is stored as 0. You’d expect it to be stored as 0 for No and 1 for
Yes, but this isn’t the case. Without a format setting, you must enter –1 or 0 , and it will be stored
and displayed that way.
You’re also able to specify a custom format for Yes/No fields. For example, assume you’ve got a
table with a field that indicates whether the employee has attended an orientation meeting.
Although a yes or no answer is appropriate, you might want to get a little fancy with the field’s dis-
play. By default, a check box is used to indicate the value of the Yes/No field (checked means

Free download pdf