MS Access —

Formatting Number, Currency, and Date/Time Fields

The Format property

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/2000; Saturday, June 10, 2000; or 10-Jun-00. Each field type has its own set of formats. For example, number fields have a different set of formats than date/time or text fields.

This lesson explains how to format number, currency and . There are two ways to format a number, currency and date/time field:

  • By selecting a ready-made format from the Format list (the easy way).

  • By typing a series of formatting characters in the Format box (the hard way).

Hopefully, the ready-made formats listed in Table will be all you will ever need to format your fields. This lesson explains how to format number, currency, and date/time fields by selecting a ready-made format.

  1. Double-click the tblCustomerTours table.

    Let's take a look at how the number and date fields in this table are currently formatted. Notice that the Cost has two decimal places and that the displays its dates in the 1/1/2000 format.

    If you haven't installed any Year 2000 updates, such as the Microsoft Office 97 Service Pack 2, your dates may be displayed in 1/1/00 format. If this is the case, you should visit the Microsoft Office Update Web site, located at www.officeupdate.com, and download Microsoft Office 97 Service Pack 2.

  2. Switch to Design view by clicking the View button on the toolbar.

    Now you can change the formats of the fields in the tblCustomerTours table.

  3. Click the Date field and click the Format box in the Field Properties section.

    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, as shown in Figure.

  4. Click the list arrow and select Medium Date.

    The Date field will now display its dates in 1-Jan-00 format instead of the 1/1/2000 format.

    You can also specify how many decimal places you want numbers in a field to display. To change the number of decimal places in a , you enter the number of decimal places you want displayed in the Decimal Places box.

  5. Click the Cost field, click the Decimal Places list arrow, and select 0.

    Now the will not display any decimal places, and Access will round any decimals equal to or greater than five to the next number.

    Depending on the field property, the Property Update Options button may appear next to the Cost field. By default, the only affects the number of decimal places that are displayednot how many decimal places are stored. If you want to change this, click the Property Update Options button and select "Update Decimal Places everywhere Cost is used" from the menu.

    You'll need to save the table before you can view your new Format settings.

    The Decimal Places property setting has no effect if the Format property is blank or is set to General Number.

  6. Click the Save button on the toolbar to save your changes.

    Now let's see how the fields look with their new .

  7. Switch to Datasheet view by clicking the View button on the toolbar.

    Notice the Date field now displays dates in a 1-Jan-00 format and the Cost field no longer has any decimal places.

Below you will find a list of ready-made number, currency, and date/time that you can choose from. These standard should be all you'll ever need if not, take a look at Lesson 4-10: Formatting Number, Currency, and Date/Time Fields by Hand.

Number, Currency, and Date/Time Formats
Number Format Example Date/Time Format Example

General Number

1234.567

General Date

6/10/2000 6:35:21 PM

Currency

$1,234.57

Long Date

Saturday, June 10, 2000

Euro

1,234.57

Medium Date

10-Jun-00

Fixed

1234.57

Short Date

6/10/2000

Standard

1,234.57

Long Time

6:35:21 PM

Percent

123456.70%

Medium Time

6:35 PM

Scientific

1.23E+03

Short Time

18:35

To format number and currency fields

  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. Click the list arrow and select a number format.

To change the number of decimal places:

  1. Make sure the table is displayed in design view and then click the field you want to format.

  2. Click the decimal places box in the field properties section.

  3. Click the list arrow and select the number of decimal places you want to display.