Access for 365 access 2021 access 2019 access 2016 accessibility 2013 access 2010 accessibility 2007 More...Less

The Number (including huge Number) and Currency Data types have a wide variety of formats to aid meet your distinct circumstances. With number and also currency formatting you have actually three choices: store the default formats, apply a predefined format, or develop a custom format. As soon as you use a layout to a table field, the same layout is automatically applied to any kind or report manage that you consequently bind to the table field. Formatting only changes how the data is displayed and also does not influence how the data is stored or just how users go into data.

You are watching: Which control format property adds comma separators and two decimal places in report design view.

In this article

Overview of number and also currency formats

To create a tradition format, you’ll enter various personalities in the Format residential property of a table field. The personalities can it is in placeholders (such as 0 and #), separators (such together periods and commas), literal meaning characters, and colors based upon how you desire the formatting to be. Simply remember that access automatically applies any type of custom formats that you usage in a table field, to a control in a kind or report if that is tied (linked).

You have the right to specify layouts for four varieties of numeric values — positive, negative, zero (0), and also null (undefined). If you choose to produce a style for each kind of value, you must put the layout for confident values first, the format for an unfavorable values second, the layout for zero worths third, and also the format for null values last. Also, you need to separate each style with a semicolon.

Example of custom formatting: #,###.##;(#,###.##);0,000.00;"Undefined"

Displays as: 1,234.568-1.234.568 = 0

Here’s what the formatting means

The number authorize (#) is a Placeholder because that digits. If there room no values, access displays a blank space. To display zeroes instead of empty spaces.For example: to screen 1234 together 1234.00, use the number 0 as the placeholder prefer this ####.00.

Positive values through two decimal places.

Negative values v two decimal places, in parentheses and also red type.

Zero worths as the number 0, constantly with two decimal places.

Null values as the word "Undefined."

Up to 4 sections are possible for a string and also each section is be separated by a semicolon (;). If her table ar does expropriate null values, you deserve to omit the fourth section.


Format Description


If the first section consists of #,###.##

Displays confident values.

1234.5678 display screens as 1,234.568

This format uses the comma as the thousands separator and the duration as the decimal separator. If the decimal worths in the record exceed the number of placeholder characters in the tradition format, access rounds the values and also displays only the variety of values specified by the format. For example, if your field has 3,456.789, but its format specifies 2 decimal places, accessibility rounds the decimal value to 0.79.

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

If the second section contains (#,###.##)

Displays only negative values.If her data does not contain negative values, accessibility leaves the ar blank.

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

If the third section consists of 0,000.00

Defines the style for every zero (0) values.

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

If the 4th section contains "Undefined"

Defines what individuals see once a record consists of a null value. In this case, users see words "Undefined."

You can also use other text, such as "Null" or "****". Surrounding characters with dual quotation marks space treated together literals and also are presented exactly as entered.

height of web page

Apply a predefined format

Access provides several predefined layouts for number and currency data. The default style is to screen the number together entered.

Tip You deserve to use the DecimalPlaces building to override the default variety of decimal locations for the predefined style specified because that the Format property.

In a table

Open the table in Design View.

In the upper ar of the style grid, pick the Date/Time ar that you desire to format.

In the Field Properties section, click the arrow in the Format home box, and also select a style from the drop-down list.

After you choose a format, the Property upgrade Options button appears, and lets girlfriend to use your new format to any kind of other table areas and kind controls that would certainly logically inherit it. To use your transforms throughout the database, click the clever tag, and then click Update Format almost everywhere is used. In this case, ar Name is the name of her number or currency field.

Save her changes and also switch come Datasheet watch to view whether the format meets her needs.

Note new forms, reports, or views that you create based upon this table acquire the table’s formatting, yet you can override this ~ above the form, report, or watch without an altering the table’s formatting.

In a form or report

In a type or report, days are usually presented in message boxes. Just collection the Format residential or commercial property for the text box to the date format friend want.

Open the form or report Layout View or Design View.

Position the reminder in the text box with the number or currency.

Press F4 to screen the building Sheet.

Set the Format residential or commercial property to among the predefined date formats.

In a query

Open the ask in Design View.

Right-click the day field, and also then click Properties.

In the property Sheet, pick the format you desire from the Format residential property list.

In one expression

optimal of page

Examples of predefined formats

The adhering to table shows the predefined Format property settings because that numbers and also currency.



General Number

(Default) screen the number as entered.


Use the thousands separator; monitor the settings specified in the local settings of home windows for negative amounts, decimal and currency symbols, and decimal places.


Use the euro symbol (

), nevertheless of the currency symbol stated in the regional settings that Windows.


Display at least one digit; monitor the setups specified in the local settings of home windows for an unfavorable amounts, decimal and also currency symbols, and decimal places.


Use the thousands separator; follow the settings specified in the regional settings of windows for an adverse amounts, decimal symbols, and decimal places.


Multiply the value by 100 and append a percent authorize (%); follow the settings specified in the regional settings of home windows for an unfavorable amounts, decimal symbols, and decimal places.


Use standard scientific notation.

The complying with are examples of the predefined number formats.




General Number


















top of page

Apply a custom format

Open the table in architecture View.

In the upper section of the design grid, pick the Date/Time field that you desire to format.

In the Field Properties section, choose the General tab, click the cell beside the Format box and enter the details characters based upon your formatting needs.

After you choose a format, the Property update Options clever tag appears (Access 2010 only), and also lets friend to use your brand-new format to any other table fields and kind controls that would certainly logically inherit it. To use your alters throughout the database, click the smart tag, and then click Update Format all over Field name is used. In this case, ar Name is the name of her Date/Time field.

Save your changes and also switch to Datasheet watch to watch whether the style meets her needs.

Test the format by law the following:

Enter values without thousands separators or decimal separators, and see exactly how the format treats the data. Walk the layout put the separators in the correct places?

Enter values that are much longer or much shorter than you anticipate (with and also without separators), and also see how the style behaves. Does the format include either unwanted empty spaces or top or rolling zeroes?

Enter a zero or a null worth in a layout meant for positive or an unfavorable values, and also see even if it is you prefer the result.

Note when you apply a format to a table field, access uses that same format in any type or report controls that you bind (link) to that field.

top of page

Examples of practice formats

The following are instances of custom number formats.




Display optimistic values normally; display an adverse values in parentheses; display the native "Null" if the worth is Null.


Display a add to (+) or minus (–) authorize with hopeful or negative numbers; display 0.0 if the value is zero.

top of web page

Custom format characters

To produce a custom format, use the following personalities as placeholders and also separators.




Used to screen a digit. Each circumstances of the personality represents a place for one number. If no worth exists in a position, accessibility displays a blank space. Also, have the right to be offered as a placeholder.

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


Used to screen a digit. Each circumstances of the character represents a position for one number. If no worth exists in a position, accessibility displays a zero (0).

Decimal separator

. (period)

Indicates whereby you want accessibility to ar the separator character between a whole and decimal component of a number or currency field.

Decimal separators vary and also are collection 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 money field.

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

blank spaces, + - $ ()

Used come insert empty spaces, math personalities (+ -), and financial symbols (¥ £ $) as needed everywhere in your format strings. If you want to usage other common math symbols, such as slash ( or /) and also the asterisk (*), surround them with dual quotation marks. Note that you deserve to place lock anywhere.


Used come force accessibility to display screen the character that immediately follows. This is the exact same as bordering a personality with twin quotation marks.


Used to force the left alignment of every values. As soon as you pressure left alignment, friend cannot usage the # and 0 number placeholders, however you deserve to use placeholders for message characters.


Used to pressure the character immediately following the asterisk to end up being a to fill character — a character provided to fill blank spaces. Accessibility normally displays numeric data together right-aligned, and it fills any area come the left of the value with empty spaces. You can add fill characters anywhere in a style string, and when you execute so, accessibility fills any blank spaces with the specified character.

For example, the format £##*~.00 screens a currency amount as £45~~~~~.15. The variety of tilde personalities (~) presented in the ar depends top top the number of blank spaces in the table field.


Used as the critical character in a style string. Multiplies the value by 100 and also displays the result with a rolling percent sign.

E+, E-


e+, e-

Used to display screen values in clinical (exponential) notation.

Use this option once the predefined scientific style doesn"t administer sufficient room for her values. Use E+ or e+ to display values as confident exponents, and also E- or e- to display an adverse exponents. You have to use these placeholders with other characters.

For example, intend that you use the layout 0.000E+00 to a numeric field and then go into 612345. Access displays 6.123E+05. Access an initial rounds the number of decimal places down to 3 (the number of zeros come the ideal or left that the decimal separator). Next, accessibility calculates the exponent value from the number of digits that fall to the right (or left, depending upon your language settings) of the decimal separator in the initial value. In this case, the original value would have actually put "612345" (five digits) to the best of the decimal point. For the reason, accessibility displays 6.123E+05, and the resulting value is the indistinguishable of 6.123 x 105.

"Literal text"

Use double quotation marks come surround any kind of text the you want individuals to see.

Used to apply a shade to all values in a ar of your format. You should enclose the surname of the shade in brackets and also use among these names: black, blue, cyan, green, magenta, red, yellow, or white.

peak of page

Display A.D. Or B.C.

You might use a custom style to display screen "A.D." prior to or "B.C." ~ a year depending upon whether a hopeful or an unfavorable number is entered. Positive numbers are presented as years v an "A.D." prior to the year. An adverse numbers are shown as years v a "B.C." after the year.

Open the table in Design View.

See more: Gimme Yo Number And I Ll Call, Soul Sand Grains: The Cost Of No Change

In the upper section of the architecture grid, pick the Number field you desire to format.

In the lower section, click the Format home box, and also then get in this custom format: