Microsoft Office Access 2007 provides several predefined formatting options that you can use to enhance the way your data appears in a table or form. This article describes the available predefined formats you can choose from, and explains how to apply the formats.
The predefined format you can select from depends on the data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) you selected for a field. Remember that the format property you apply will only affect how the data is displayed and not how Access stores the underlying data in the tables. For example, if you select a format that displays only two decimal places in a Currency data type and the data source contains four decimal places, you will see a rounded value displayed while the actual values in the table would be different.
Since Access has a default format for every data type, you would need to change the formatting only if you want to change the default display. Applying a specific format can be useful when you sometimes want data to be displayed with some additional details. For example, in a purchase order table, if you want to indicate that the quantity of a product is available only by the crate, you can apply a custom format so that the word & crate(s) is displayed when a quantity is entered in that field. Displaying a type of quantity, such as crate(s), can help to avoid confusion about the quantity ordered.
Note The Microsoft Windows regional settings will also influence how your Date/Time, Number, and Currency data types will display when users apply formatting options. Windows supports a variety of languages, plus Currency and Date/Time formats for the countries/regions that use those languages. For more information on the Windows regional settings, see Change the Windows regional settings to modify the appearance of some data types.
Many data types have predefined Access formatting options. For data types where predefined formatting is not available, you can specify a custom format by entering sets of literal and placeholder characters in a field.
Let us now take a look at the available predefined formats and how to apply them.
Select a predefined format
Choose a format for the Number and Currency data types
If you don't choose a specific Format property setting for a field or control that displays a number or a currency value, Access displays the number values in the General Number format and the currency values in the Currency format.
The following table lists and explains the available predefined formats for the Number (Number data type: In a Microsoft Access database, a field data type designed for numerical data that will be used in mathematical calculations. Use the Currency data type, however, to display or calculate currency values.) and Currency (Currency data type: In a Microsoft Access database, a data type that is useful for calculations involving money or for fixed-point calculations in which accuracy is extremely important.) data types.
||(Default) displays the number as entered. Display up to 11 digits to the right or left of the decimal indicator. If a number contains more than 11 digits, or your control is not wide enough to show all the digits, Access rounds the number. For very large or very small numbers (more than 10 digits to the right or left of the decimal point), Access uses scientific notation.
||Displays the currency symbol and format as specified in your Windows regional settings.
||Applies the euro symbol (€) to numeric data. Otherwise use the currency format specified in your Windows regional settings.
||Displays numbers without the thousand separators and with two decimal places. If the value in the field contains more than two decimal places, Access rounds down the number.
||Displays numbers with the thousand separators and two decimal places. If the value in the field contains more than two decimal places, Access rounds down the number to two decimal places.
||Displays numbers as percentages with two decimal places and a trailing percent sign. If the underlying value contains more than four decimal places, Access rounds down the value.
||Displays numbers with scientific (exponential) notation.
Choose a format for the Date/Time data type
You might want to choose a specific format to control how the date and time values display. For example, when a field or control is set to the Date/Time data type (Date/Time data type: An Access database data type that is used to hold date and time information.), and you select the Short Date format option, dates may appear as 8/24/2007. If you select the Long Date format, that same date may appear as Wednesday, August 24, 2007.
If you do not specify a Format property setting for the Date/Time data type, Access displays the date and time values in the General Date format.
The following table lists and describes the six predefined formats that you can choose from for the Date/Time data type.
||(Default) Use to display date values as numbers and time values as hours, minutes, and seconds, followed by AM or PM. For both types of values, Access uses the date and time separators specified in your Windows regional settings. If the value does not have a time component, Access displays only the date. If the value has no date component, Access displays only the time.
||08/29/2006 10:10:42 AM
||Use to display only date values as specified by the Long Date format in your Windows regional settings.
||Monday, August 29, 2006
||Use to display the date as dd/mmm/yyyy, but use the date separator specified in your Windows regional settings.
||Use to display date values as specified by the Short Date format in your Windows regional settings.
||Use to display hours, minutes, and seconds, followed by AM or PM. Access uses the separator specified in the Time setting in your Windows regional settings.
||Use to display hours and minutes, followed by AM or PM. Access uses the separator specified in the Time setting in your Windows regional settings.
||Use to display only hours and minutes. Access uses the separator specified in the Time setting in your Windows regional settings.
Choose a format for the Yes/No data type
You can select from one of the three predefined formats for displaying the values for the Yes/No data type (Yes/No data type: A field data type that you use for fields that will contain only one of two values, such as Yes or No and True or False. Null values are not allowed.). You may also want to consider using check boxes or option buttons instead of a text box to display the Yes/No values.
||(Default). Displays 0 as No and any nonzero value as Yes.
||Displays 0 as False and any nonzero value as True.
||Displays 0 as Off and any nonzero value as On.
You can also apply custom formats such as using custom words or phrases for the Yes/No values or even apply different colors for the values by specifying a string.
Apply a predefined format
Before you apply a format to a field or control, remember that the formatting affects only the visual appearance of the data and not how Access stores the data and that your Windows regional settings may control part or all of how the values are displayed.
To apply a predefined format, do the following:
- In the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu.
- Click the field that you want to format, and then, on the General tab, click the cell next to the Format box.
- Click the arrow and then select a format in the list.
The Property Update Options smart tag appears.
Note If the format list is blank, specific data type does not offer any predefined formats and Access applies a default format for that data type.
- You can click the Property Update Options smart tag and choose to update the format everywhere the field is used throughout your database (such as a control on a form), or you can limit the format change only to the table.
- Save your changes.
Note When you apply a format to a table field, Access uses that same format in any form or report controls that you bind (link) to that field.
Top of Page