
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).
Format Number, Currency, and Date Fields
- In Design View, click the field you want to format.
- Click the Format box.
A list arrow appears in the Format box. You can format this field the easy way by clicking the arrow to select from a list of ready-made number formats.
- Click the Format box list arrow and select a format.
- In Design view, click the field you want to format.
- Click the Decimal Places box.
- Click the Decimal Places list arrow and select the number of decimal places you want to display.
- In Design View, click the field you want to format.
- 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.”
- Enter the formatting characters for how you want the number to be formatted.
Number Fields | |
---|---|
Number Format: | Example: |
General Format | 1234.567 |
Currency | $1,234.57 |
Euro | €1,234.57 |
Fixed | 1234.57 |
Standard | 1,234.57 |
Percent | 123456.70% |
Scientific | 1.23E+03 |
Date/Time Fields | |
---|---|
Date/Time Format: | Example: |
General Date | 6/10/2017 6:35:21 PM |
Long Date | Saturday, June 10, 2017 |
Medium Date | 10-Jun-17 |
Short Date | 6/10/2017 |
Long Time | 6:35:21 PM |
Medium Time | 6:35 PM |
Short Time | 18:35 |
Change the Number of Decimal Places
You can also specify how many decimal places you want numbers to display in a field.
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.
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 |
FREE Quick Reference
Click to DownloadFree to distribute with our compliments; we hope you will consider our paid training.