Format Number, Currency, and Date/Time Fields

A field’s Format property changes how information appears in the field, not how the data is actually stored in the field. For example, a date field could be formatted to display the same value as 6/10/2020; Saturday, June 10, 2020; or 10-Jun-20. Each field type has its own set of formats.

There are two ways to format a number, currency, and date/time field:

• Selecting a ready-made format from the Format list (the easy way). For most people, the ready-made formats listed in the Number and Date/Time Formats table below will be all you will ever need to format your fields.
• Typing a series of formatting characters by hand in the Format box (the more difficult way).

Change the Number of Decimal Places

You can also specify how many decimal places you want numbers to display in a field.

1. In Design view, click the field you want to format.
2. Click the Decimal Places box.
3. Click the Decimal Places list arrow and select the number of decimal places you want to display.
   

Now Access will only display the number of decimals you selected.

Format Number, Currency, and Currency Fields by Hand

If none of the ready-made number, currency, or date/time formats meet your needs, you can format fields the old-fashioned way—by hand.

1. In Design View, click the field you want to format.
2. Click the Format box in the Field Properties section.

   You can use the table below to help you know what characters to enter. You can mix and match any of the characters—for example, you could add “mmmm” (full name of month) to “yy” (last two digits of the year) to get “January 00.”

3. Enter the formatting characters for how you want the number to be formatted.
   

Number, Currency, and Date/Time Operators Formatting Characters
Character	Description	Format	Display
:	Time separator	h:nn	8:45
/	Date separator	m/d/yy	10/8/17
-	Date separator	m-d-yy	10-8-17
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
yyy	Full year	mmmm d, yyyy	March 15, 2000
h	Hour in one or two digits	h:nn	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




Number, Currency, and Date/Time Operators Formatting Characters
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 a 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	50000	#.00E+00	5.00E+.05