Customize data formats in Access

The data in your database comes with predefined formats but when you want something different, you can use custom formats. Since custom formatting only changes how the data is displayed, you don’t have to worry about any changes to the data. This article explains what types of characters are available and how you can use them.

Note:  Custom formatting options are not available for Access apps.

What do you want to do?

Learn more about custom formats

Formats for Number and Currency data types

Formats for Text data types

Formatting Date/Time data type

Learn more about custom formats

To create a custom format, you’ll enter various characters in the Format property of a table field. The characters can be placeholders (such as 0 and #), separators (such as periods and commas), literal characters, and colors based on how you want the formatting to be. Just remember that Access automatically applies any custom formats that you use in a table field, to a control in a form or report if it is bound (linked).

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 formatting: #,###.##;(#,###.##)[Red];0,000.00;"Undefined"

Displays as: 1,234.568-1.234.568 = 0

Here’s what the formatting means

  • The number sign (#) is a Placeholder for digits. If there are no values, Access displays a blank space. To display zeroes instead of blank spaces.
    For example: to display 1234 as 1234.00, use the number 0 as the placeholder like this ####.00.

  • Positive values with two decimal places.

  • Negative values with two decimal places, in parentheses and red type.

  • Zero values as the number 0, always with two decimal places.

  • Null values as the word "Undefined."

Up to four sections are possible for a string and each section is separated by a semicolon (;). If your table field does accept null values, you can omit the fourth section.

Section

Format Description

Example

If the first section contains #,###.##

Displays positive values.

1234.5678 displays as 1,234.568

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

Tip:  For a format with larger values or more decimal places, add more placeholders for the decimal value, such as #,###.###.

If the second section contains (#,###.##)[Red]

Displays only negative values.
If your data does not contain negative values, Access leaves the field blank.

The negative value is enclosed within literal characters or parentheses. In this example, any negative value will be displayed in red color.

If the third section contains 0,000.00

Defines the format for all zero (0) values.

When the field contains a value of zero, 0,000.00 is displayed. To display text instead of a number, use "Zero" (surrounded by double quotation marks).

If the fourth section contains "Undefined"

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 "****". Surrounding characters with double quotation marks are treated as literals and are displayed exactly as entered.

Top of Page

Formats for Number and Currency data types

To create a custom format, use the formatting characters shown in the following table.

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.

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.

 *

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.

Apply custom formatting to Number or Currency data types:

  1. Open the table in Design View and select the field that you want to format,

  2. On the General tab, click the cell next to the Format box and enter the specific characters based on your formatting needs.

  3. Press CTRL+S to save your work.

Top of Page

Formats for Text data types

The Text and Memo data types do not accept predefined formats, but you can apply custom formats 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. The Text and Memo data types allow two format sections in a string. The first section of the string controls how the text appears, and the second section controls how empty values or zero-length strings appear. 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 Text 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.

Apply custom formats to text fields

  1. Open the table in Design View.

  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.

  4. Press CTRL+S to save your work.

Top of Page

Formatting Date/Time data type

When you don't specify either a predefined or custom format for the date/time data type, Access automatically applies the 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. 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.

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.

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.

For more information, see formatting dates and times.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×