Format Number, Currency, and Date/Time Fields

How to Format Number, Currency, and Date/Time Fields in Access

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).

Format Number, Currency, and Date Fields

  1. In Design View, click the field you want to format.
  2. 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.

  3. Click the Format box list arrow and select a format.
    Format Number, Currency, and Date/Time Fields
  4. 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.

  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.
    Format Number, Currency, and Date/Time Fields

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.
    Format Number, Currency, and Date/Time Fields
  4. 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 Download