Specify how data displays by using custom formatting

You have the data in a field appear in a specific format by applying custom formats. Custom formatting only change how the data is displayed and does not affect how the data is stored in a Microsoft Access database, or how users can enter or edit data.

Applying a custom format is an option when you work with data types that either do not have a predefined Access formatting option or if a predefined format does not meet your needs. You can apply custom formats by entering sets of literal characters and placeholder characters to a field. When you apply a format to a table field, that same format is automatically applied to any form or report control that you bind (link) to that table field.

This article describes the types of literal and placeholder characters that you can use with specific data types and shows how to apply the formatting.

What do you want to do?


Learn more about custom formats

When you create a custom format, you enter various characters in the Format property of a table field. The characters consist of placeholders (such as 0 and #), separators (such as periods and commas), literal characters, and colors.

In addition, you can specify formats for four types of numeric values — positive, negative, zero (0), and null (undefined). If you choose to create a format for each type of value, you must put the format for positive values first, the format for negative values second, the format for zero values third, and the format for null values last. Also, you must separate each format with a semicolon.

Example of custom formats    

#,###.##;(#,###.##)[Red];0,000.00;"Undefined"

This string has four custom formats and displays the following:

Each of the four sections in the string is separated by a semicolon (;).

The the following describes each section:

  • The number sign (#) is a placeholder for digits. If the format encounters no values, Access displays a blank space. If you want to display zeroes instead of blank spaces (for example, to display 1234 as 1234.00), you use the number 0 as the placeholder.
  • By default, the first section displays positive values. If you want to use a format with larger values or more decimal places, you can add more placeholders for the decimal value, such as #,###.###. As an example, 1234.5678 is displayed as 1,234.568. Notice that this format uses the comma as the thousands separator and the period as the decimal separator.

If the decimal values in the record exceed the number of placeholder characters in the custom format, Access rounds the values and displays only the number of values specified by the format. For example, if your field contains 3,456.789, but its format specifies two decimal places, Access rounds the decimal value to .79.

  • By default, the second section displays only negative values. If your data does not contain negative values, Access leaves the field blank. The preceding example format surrounds any negative values with literal characters — the opening and closing parentheses. It also uses the [Red] color declaration to display any negative values in red type.
  • By default, the third section defines the format for all zero (0) values. In this case, when the field contains a value of zero, 0,000.00 is displayed. To display text instead of a number, you can use "Zero" (surrounded by double quotation marks) instead.
  • By default, the fourth section defines what users see when a record contains a null value. In this case, users see the word "Undefined." You can also use other text, such as "Null" or "****". Keep in mind that surrounding characters with double quotation marks causes the format to treat those characters as literals and display them exactly as you entered them.

 Note   Remember that you do not need to use all four sections. For example, if your table field does accept null values, you can omit the fourth section.

Top of Page Top of PageTop of Page Top of Page

Specify a format for Number and Currency data types

If you don't specify a custom format for numeric and currency values, Access displays numbers in the General Number format and currencies in the Currency format.

To create a custom format, use the formatting characters shown in the following table. You can also specify a color in which you want the number or currency.

Character Description
#

Used to display a digit. Each instance of the character represents a position for one number. If no value exists in a position, Access displays a blank space. Also, can be used as a placeholder.

For example, if you apply the format #,### and enter a value of 45 in the field, 45 is displayed. If you enter 12,145 in a field, Access displays 12,145 — even though you defined only one placeholder to the left of the thousands separator.

0 Used to display a digit. Each instance of the character represents a position for one number. If no value exists in a position, Access displays a zero (0).
Thousands and decimal separators Used to indicate where you want Access to place the thousands and decimal separators. Access uses the separators that are defined for your Windows regional settings. For information about those settings, see Change the Windows regional settings to modify the appearance of some data types.
blank spaces, + - $ () Used to insert blank spaces, math characters (+ -), and financial symbols (¥ £ $) as needed anywhere in your format strings. If you want to use other common math symbols, such as slash (\ or /) and the asterisk (*), surround them with double quotation marks. Note that you can place them anywhere.
 \ Used to force Access to display the character that immediately follows. This is the same as surrounding a character with double quotation marks.
 ! Used to force the left alignment of all values. When you force left alignment, you cannot use the # and 0 digit placeholders, but you can use placeholders for text characters. For more information about those placeholders, see Custom formats for Text and Memo data types.
 *

Used to force the character immediately following the asterisk to become a fill character — a character used to fill blank spaces. Access normally displays numeric data as right-aligned, and it fills any area to the left of the value with blank spaces. You can add fill characters anywhere in a format string, and when you do so, Access fills any blank spaces with the specified character.

For example, the format £##*~.00 displays a currency amount as £45~~~~~.15. The number of tilde characters (~) displayed in the field depends on the number of blank spaces in the table field.

 % Used as the last character in a format string. Multiplies the value by 100 and displays the result with a trailing percent sign.

E+, E-

–or–

e+, e-

Used to display values in scientific (exponential) notation.

Use this option when the predefined scientific format doesn't provide sufficient room for your values. Use E+ or e+ to display values as positive exponents, and E- or e- to display negative exponents. You must use these placeholders with other characters.

For example, suppose that you apply the format 0.000E+00 to a numeric field and then enter 612345. Access displays 6.123E+05. Access first rounds the number of decimal places down to three (the number of zeros to the right or left of the decimal separator). Next, Access calculates the exponent value from the number of digits that fall to the right (or left, depending on your language settings) of the decimal separator in the original value. In this case, the original value would have put "612345" (five digits) to the right of the decimal point. For that reason, Access displays 6.123E+05, and the resulting value is the equivalent of 6.123 x 105.

"Literal text" Use double quotation marks to surround any text that you want users to see.
[color] Used to apply a color to all values in a section of your format. You must enclose the name of the color in brackets and use one of these names: black, blue, cyan, green, magenta, red, yellow, or white.

To apply a custom format to the Number or Currency data types, do the following:

  1. In the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu.
  2. Select the field that you want to format, and on the General tab, click the cell next to the Format box.
  3. Enter the specific characters based on your formatting needs.
  4. Press CTRL+S to save your work.

After you apply a custom format to a field, you can test the format by doing any or all of the following:

  • Enter values without thousands separators or decimal separators, and see how the format treats the data. Does the format put the separators in the correct places?
  • Enter values that are longer or shorter than you anticipate (with and without separators), and see how the format behaves. Does the format add either unwanted blank spaces or leading or trailing zeroes?
  • Enter a zero or a null value in a format meant for positive or negative values, and see whether you like the result.

Top of Page Top of Page

Specify a format for Text and Memo data types

The Text (Text data type: In a Microsoft Access database, this is a field data type. Text fields can contain up to 255 characters or the number of characters specified by the FieldSize property, whichever is less.) and Memo (Memo data type: In a Microsoft Access database, this is a field data type. Memo fields can contain up to 65,535 characters.) data types do not accept predefined formats. The Text data type accepts only custom formats, the Memo data type accepts both custom and Rich Text formatting.

Typically, you apply custom formats to Text and Memo data types to make the table data easier to read. For example, if you use a Web form to collect credit card numbers, and you store those numbers without spaces, you can use a custom format to add the appropriate spaces to make the credit card numbers easier to read.

Custom formats for the Text and Memo data types allow only two format sections in a string. The first section of a format string controls the appearance of text, and the second section displays empty values or zero-length strings. If you don't specify a format, Access left aligns all text in datasheets.

The following table lists and explains the custom formats that you can apply to fields that have the Text or Memo data types.

Character Description
@

Used to display any available character for its position in the format string. If Access places all characters in the underlying data, any remaining placeholders appear as blank spaces.

For example, if the format string is @@@@@ and the underlying text is ABC, the text is left-aligned with two leading blank spaces.

&

Used to display any available character for its position in the format string. If Access places all characters in the underlying data, any remaining placeholders display nothing.

For example, if the format string is &&&&& and the text is ABC, only the left-aligned text is displayed.

! Used to force placeholder characters to be filled from left to right instead of right to left. You must use this character at the start of any format string.
< Used to force all text to lowercase. You must use this character at the beginning of a format string, but you can precede it with an exclamation point (!).
> Used to force all text to uppercase. You must use this character at the beginning of a format string, but you can precede it with an exclamation point (!).
* When used, the character immediately after the asterisk (*) becomes a fill character — a character used to fill blank spaces. Access normally displays text as left-aligned and fills any area to the right of the value with blank spaces. You can add fill characters anywhere in a format string. When you do so, Access fills any blank spaces with the specified character.
Blank space, + - $ () Used to insert blank spaces, math characters (+ -), financial symbols ($ ¥ £), and parentheses as needed anywhere in your format strings. If you want to use other common math symbols, such as slash (\ or /) and the asterisk (*), surround them with double quotation marks — note that you can place these characters anywhere in the format string.
"Literal text" Use double quotation marks to surround any text that you want displayed to users.
\ Used to force Access to display the character that immediately follows. This is the same as surrounding a character with double quotation marks.
[color] Used to apply a color to all values in a section of your format. You must enclose the name in brackets and use one of these names: black, blue, cyan, green, magenta, red, yellow, or white.

 Note   When you specify a format, Access fills the placeholder characters with data from the underlying field.

To apply a custom format, do the following:

  1. In the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu.
  2. Select the field that you want to format, and on the General tab, click the cell next to the Format box.
  3. Enter your format.

Type of custom format that you can specify depends on the data type that you select for the field.

  1. Press CTRL+S to save your work.

You can test your custom formatting in any of the following ways:

  • Enter uppercase or lowercase values, and see how the format treats the data. Do the results make sense?
  • Enter values that are longer or shorter than you anticipate (with and without separators), and see how the format behaves. Does the format add either unwanted blank spaces or leading or trailing spaces, or unexpected characters?
  • Enter a zero-length string or a null value, and see whether you like the result.

Top of Page Top of Page

Specify a format for Date/Time data type

If you don't specify a predefined or custom format, Access applies the General Date format — m/dd/yyyy h:nn:ss AM/PM.

Custom formats for the Date/Time fields can contain two sections — one for the date and another for time — and you separate the sections with a semicolon. For example, you can re-create the General Date format as follows: m/dd/yyyy;h:nn:ss.

Character Description
Date separator Used to control where Access places the separator for days, months, and years. Access uses the separator defined in your Windows regional settings. For information about those settings, see Change the Windows regional settings to modify the appearance of some data types.
c Used to display the general date format.
d or dd Used to display the day of the month as one or two digits. For one digit, use a single placeholder. For two digits, use two placeholders.
ddd

Used to abbreviate the day of the week to three letters.

For example, Monday appears as Mon.

dddd Used to spell out all days of the week.
ddddd Used to display the Short Date format.
dddddd Used to display the Long Date format.
w

Used to display the number of the day of the week.

For example, Monday appears as 2.

m or mm Used to display the month as either a one-digit or two-digit number.
mmm

Used to abbreviate the name of the month to three letters.

For example, October appears as Oct.

mmmm Used to spell out all month names.
q

Used to display the number of the current calendar quarter (1-4).

For example, for a date in May, Access displays 2 as the quarter value.

y Used to display the day of the year (1-366).
yy

Used to display the last two digits of the year.

 Note   We recommend entering and displaying all four digits of a given year.

yyyy Used to display all digits in a year in the range 0100-9999.
Time separator Used to control where Access places the separator for hours, minutes, and seconds. Access uses the separator defined in your Windows regional settings. .
h or hh Used to display the hour as one or two digits.
n or nn Used to display minutes as one or two digits.
s or ss Used to display seconds as one or two digits.
ttttt Used to display the Long Time format.
AM/PM Used to display 12-hour clock values with a trailing AM or PM. Access relies on the system clock in your computer to set the value.
A/P or a/p Used to display 12-hour clock values with a trailing A, P, a, or p. Access relies on the system clock in your computer to set the value.
AMPM Used to display 12-hour clock values. Access uses the morning and afternoon indicators specified in your Windows regional settings.
Blank space, + - $ () Used to insert blank spaces, math characters (+ -), financial symbols ($ ¥ £), and parentheses as needed anywhere in your format strings. If you want to use other common math symbols, such as slash (\ or /) and the asterisk (*), surround them with double quotation marks. Note that you can place them anywhere.
\ Used to force Access to display the character that immediately follows. This is the same as surrounding a character with double quotation marks.
* Used to force the character immediately following the asterisk to become a fill character — a character used to fill blank spaces. Access normally displays text as left aligned and fills any area to the right of the value with blank spaces. You can add fill characters anywhere in a format string. When you do so, Access fills any blank spaces with the specified character.
"Literal text" Use double quotation marks to surround any text that you want users to see.
[color] Used to apply a color to all values in a section of your format. You must enclose the name in brackets and use one of these names: black, blue, cyan, green, magenta, red, yellow, or white.

Top of Page Top of PageTop of Page Top of Page

Learn more about formatting options

If you want a field in a datasheet, form, or report to display a certain value while the field stores a different value, create a lookup field. To learn more about lookup fields, read Customize how data is displayed by creating a lookup field. To control the format in which data is entered, read Create an input mask to enter field or control values in a specific. To learn how to add rich text formatting to a field, read Insert or add a rich text field.

Top of Page Top of PageTop of Page Top of Page

 
 
Applies to:
Access 2010, Access 2007