Lesson4.10.Formatting Number, Currency, and DateTime Fields by Hand

Lesson 4.10. Formatting Number, Currency, and Date/Time Fields by Hand

Figure 4-13. The Format property

If none of the ready-made number, currency, or date/time formats meet your needs, you can format your number, currency, and date/time fields the old-fashioned wayby hand. Formatting fields by hand is a laborious and confusing processyou tell Access how you want the field to be formatted by typing the appropriate formatting characters in the Format box. On the other hand, manually formatting a number, currency, or date/time field gives you complete flexibility on how the field displays its information.

In this lesson you will learn how to format number, currency, and date/time fields by hand.

1. Click the Design button on the toolbar to switch to Design view.

   Now you need to click the field you want to format.

2. Click the Date field.

   This time we will specify our own date/time format instead of using one of the Format Property box's ready-made data formats. Formatting number, currency, and date/time fields by hand isn't exactly a difficult process, but you will probably need to use Table 4-5 and Table 4-6 on the next page in order to know which formatting characters to enter.

3. Click the Format box in the Field Properties section and type ddd mmm d.

   Your table should look like Figure 4-13. This will display the date in Sun Mar 8 format. Look at Table 4-5 for a description of the characters you just entered

4. Click the Save button on the toolbar to save your changes, and then switch to Datasheet view by clicking the View button on the toolbar.

   Notice the Date field now displays its days using the Date format you specified.

5. Close the tblCustomerTours table.

Use the characters in the following table when you want to format number, currency, or date/time characters by hand. You can mix and match any of the following characters. For example, you could add "mmmm" (full name of month) to "yy" (last two digits of the year) to get "January 00."

Table 4-5. Date/Time Formatting Characters

Character	Description	Format	Display
:	Time separator	h: nn	8:45
/	Date separator	m/d/yy	10/8/00
-	Date separator	m-d-yy	10-8-00
d	Day in one or two numeric digits	m/d/yy	10/8/00
dd	Day in two numeric digits	m/dd/yy	10/08/00
ddd	First three letters of the weekday	ddd, m/d/yy	Sun, 3/8/00
dddd	Full name of the weekday	dddd, m/d/yy	Sunday, 3/8/00
m	Month in one or two digits	m/d/yy	3/15/00
mm	Month in two digits	mm/dd/yy	03/15/00
mmm	First three letters of the month	mmm-d-yy	Mar-15-00
mmmm	Full name of the month	mmmm d, yyyy	March 15, 2000
yy	Last two digits of the year	m/d/yy	3/15/00
yyyy	Full year	mmmm d, yyyy	March 15, 2000
h	Hour in one or two digits	h:n	8:45
hh	Hour in two digits	hh:nn	08:45
nn	Minute in two digits	hh:nn	13:09
ss	Second in two digits	hh:nn:ss	10:45:07
AM/PM	Twelve-hour clock (uppercase)	hh:nn AM/PM	08:45 AM
am/pm	Twelve-hour clock (lowercase)	hh:nn am/pm	08:45 am

Table 4-6. Number Formatting Symbols

Character	Description	Data	Format	Display
#	Display a digit or nothing	50	#	50
0	Display a digit or 0	50	#.00	50.00
.	Display a decimal separator	50	#.	50.
,	Display thousands separator	5000	#,###	5,000
$	Display the $ currency symbol	50	$#.00	$50.00
%	Multiply the value by 100 and add a percent sign	0.5	#%	50%
E-, E+, e-, e+	Scientific notation	500000	#.00E+00	5.00E+05

QUICK REFERENCE

TO MANUALLY FORMAT A DATE/TIME, NUMBER, OR CURRENCY FIELD:

1. MAKE SURE THE TABLE IS DISPLAYED IN DESIGN VIEW AND CLICK THE FIELD YOU WANT TO FORMAT.

2. CLICK THE FORMAT BOX IN THE FIELD PROPERTIES SECTION.

3. ENTER THE APPROPRIATE FORMATTING CHARACTERS OR SYMBOLS FOR HOW YOU WANT THE DATE OR NUMBER TO BE FORMATTED.