Format Property - Number and Currency Data Types

You can set the Format property to predefined number formats or custom number formats for the Number (including Large Number) and Currency data types.

Setting

You can use a predefined format or create a custom format.

Predefined Formats

The following table shows the predefined Format property settings for numbers.

Setting

Description

General Number

(Default) Display the number as entered.

Currency

Use the thousand separator; follow the settings specified in the regional settings of Windows for negative amounts, decimal and currency symbols, and decimal places.

Euro

Use the euro symbol ( euro ), regardless of the currency symbol specified in the regional settings of Windows.

Fixed

Display at least one digit; follow the settings specified in the regional settings of Windows for negative amounts, decimal and currency symbols, and decimal places.

Standard

Use the thousand separator; follow the settings specified in the regional settings of Windows for negative amounts, decimal symbols, and decimal places.

Percent

Multiply the value by 100 and append a percent sign (%); follow the settings specified in the regional settings of Windows for negative amounts, decimal symbols, and decimal places.

Scientific

Use standard scientific notation.


Custom Formats

You can create custom number formats by using the following symbols.

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

Decimal separator

. (period)

Indicates where you want Access to place the separator character between a whole and decimal part of a number or currency field.

Decimal separators vary and are set in the regional settings in Windows.

Thousands separator

, (comma)

Indicates where you want Access to place the separator character between the thousands part of a number or currency field.

Thousands separators vary and are set in the regional settings in Windows.

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.

Remarks

Custom number formats can have one to four sections with semicolons (;) as the list separator. Each section contains the format specification for a different type of number.

Section

Description

First

The format for positive numbers.

Second

The format for negative numbers.

Third

The format for zero values.

Fourth

The format for Null values.

For example, you could use the following custom Currency format:

$#,##0.00[Green];($#,##0.00)[Red];"Zero";"Null"

This number format contains four sections separated by semicolons and uses a different format for each section.

If you use multiple sections but don't specify a format for each section, entries for which there is no format will either display nothing or default to the formatting of the first section.

You can use the DecimalPlaces property to override the default number of decimal places for the predefined format specified for the Format property.

The predefined currency and euro formats follow the settings in the regional settings of Windows. You can override these by entering your own currency format.

Examples

The following are examples of the predefined number formats.

Setting

Data

Display

General Number

3456.789
–3456.789
$213.21

3456.789
–3456.789
$213.21

Currency

3456.789
–3456.789

$3,456.79
($3,456.79)

Fixed

3456.789
–3456.789
3.56645

3456.79
–3456.79
3.57

Standard

3456.789

3,456.79

Percent

3
0.45

300%
45%

Scientific

3456.789
–3456.789

3.46E+03
–3.46E+03


The following are examples of custom number formats.

Setting

Description

0;(0);;"Null"

Display positive values normally; display negative values in parentheses; display the word "Null" if the value is Null.

+0.0;–0.0;0.0

Display a plus (+) or minus (–) sign with positive or negative numbers; display 0.0 if the value is zero.

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×