The Number (including Large Number) and also Currency Data Types have a large selection of layouts to aid meet your distinctive scenarios. With number and also money formatting you have actually 3 choices: keep the default layouts, apply a predefined format, or create a practice format. When you apply a format to a table area, that exact same format is instantly used to any develop or report control that you subsequently bind to that table area. Formatting only changes exactly how the data is presented and also does not affect just how the data is stored or exactly how users enter information.

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

In this article

Rundown of number and also money formats

To create a custom format, you’ll enter various personalities in the Format residential property of a table area. The characters can be placeholders (such as 0 and #), separators (such as durations and also commas), literal personalities, and also colors based upon just how you desire the formatting to be. Just remember that Access automatically applies any kind of tradition formats that you usage in a table field, to a manage in a kind or report if it is bound (linked).

You have the right to specify formats for 4 types of numeric values — positive, negative, zero (0), and also null (undefined). If you choose to develop a format for each kind of worth, you should put the format for positive values first, the format for negative worths second, the format for zero values third, and the format for null values last. Also, you must sepaprice each format via a semicolon.

Example of practice formatting: #,###.##;(#,###.##);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 tright here are no values, Access screens a empty area. To display screen zeroes rather of empty spaces.For example: to display 1234 as 1234.00, use the number 0 as the placeholder favor this ####.00.

Confident worths with 2 decimal locations.

Negative worths through two decimal places, in parentheses and red kind.

Zero worths as the number 0, constantly via two decimal locations.

Null values as the word "Undefined."

Up to 4 sections are feasible for a string and also each area is separated by a semicolon (;). If your table field does accept null worths, you can omit the fourth section.


Style Description


If the initially area has #,###.##

Displays positive values.

1234.5678 displays as 1,234.568

This format supplies the comma as the thousands separator and also the period as the decimal separator. If the decimal values in the document exceed the number of placeholder personalities in the custom format, Access rounds the values and screens only the variety of values stated by the format. For instance, if your field includes 3,456.789, yet its format states two decimal places, Access rounds the decimal value to 0.79.

Tip: For a format via bigger values or even more decimal places, include even more placeholders for the decimal worth, such as #,###.###.

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

Displays just negative worths.If your information does not contain negative values, Access leaves the field empty.

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

If the third section includes 0,000.00

Defines the format for all zero (0) values.

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

If the fourth area contains "Undefined"

Defines what users see once a record contains a null worth. In this instance, individuals check out the word "Unidentified."

You can likewise usage various other message, such as "Null" or "****". Surrounding characters through double quotation marks are treated as literals and also are presented exactly as gotten in.

Top of Page

Apply a predefined format

Access gives several predefined layouts for number and also currency data. The default format is to display screen the number as gotten in.

Tip You have the right to use the DecimalPlaces property to override the default variety of decimal locations for the preidentified format stated for the Format residential or commercial property.

In a table

Open the table in Deauthorize View.

In the upper section of the design grid, select the Date/Time area that you want to format.

In the Field Properties area, click the arrow in the Format building box, and also pick a format from the drop-dvery own list.

After you choose a format, the Property Upday Options switch appears, and allows you to apply your new format to any type of other table areas and also form controls that would logically inherit it. To apply your changes throughout the database, click the smart tag, and also then click Upday Layout anywhere is used. In this instance, Field Name is the name of your number or money field.

Save your changes and also switch to Datasheet see to view whether the format meets your demands.

Note New develops, reports, or views that you create based upon this table acquire the table’s formatting, but you deserve to override this on the develop, report, or check out without changing the table’s formatting.

In a kind or report

In a type or report, dates are usually presented in message boxes. Just collection the Format property for the message box to the day format you desire.

Open the develop or report Layout View or Design View.

Position the tip in the message box with the number or money.

Press F4 to display screen the Property Sheet.

Set the Format home to among the precharacterized date styles.

In a query

Open the query in Deauthorize View.

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

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

In an expression

Top of Page

Instances of precharacterized formats

The following table shows the predefined Format residential or commercial property settings for numbers and currency.



General Number

(Default) Display the number as entered.


Use the thousand separator; follow the settings stated in the regional settings of Windows for negative amounts, decimal and also currency signs, and decimal locations.


Use the euro symbol (

), regardless of the money symbol stated in the local settings of Windows.


Display at leastern one digit; follow the settings mentioned in the local settings of Windows for negative amounts, decimal and also currency signs, and decimal areas.


Use the thousand also separator; follow the settings specified in the local settings of Windows for negative amounts, decimal signs, and also decimal areas.


Multiply the value by 100 and also appfinish a percent authorize (%); follow the settings stated in the neighborhood settings of Windows for negative amounts, decimal icons, and also decimal locations.


Use standard clinical notation.

The complying with are examples of the preidentified number styles.




General Number


















Top of Page

Apply a tradition format

Open the table in Deauthorize View.

In the top section of the style grid, select the Date/Time area that you want to format.

In the Field Properties section, pick the General tab, click the cell beside the Format box and also enter the particular characters based upon your formatting requirements.

After you choose a format, the Property Upday Options smart tag shows up (Access 2010 only), and allows you to use your brand-new format to any kind of various other table fields and also create controls that would certainly logically inherit it. To use your alters throughout the database, click the smart tag, and then click Upday Layout everywhere Field Name is used. In this situation, Field Name is the name of your Date/Time area.

Save your changes and also switch to Datasheet view to see whether the format meets your needs.

Test the format by doing the following:

Go into worths without thousands separators or decimal separators, and see just how the format treats the data. Does the format put the separators in the correct places?

Enter worths that are much longer or shorter than you anticipate (via and without separators), and watch just how the format behaves. Does the format include either undesirable empty spaces or leading or trailing zeroes?

Enter a zero or a null value in a format expected for positive or negative worths, and check out whether you like the result.

Note When you apply a format to a table field, Access supplies that very same format in any type of develop or report controls that you bind (link) to that area.

Top of Page

Examples of custom formats

The complying with are examples of tradition number formats.




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


Display a plus (+) or minus (–) authorize via positive or negative numbers; display screen 0.0 if the worth is zero.

Top of Page

Custom format characters

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




Used to screen a digit. Each instance of the character represents a place for one number. If no worth exists in a position, Access display screens a empty room. Also, deserve to be provided as a placeholder.

For example, if you apply the format #,### and enter a worth of 45 in the field, 45 is presented. If you enter 12,145 in a field, Access displays 12,145 — also though you identified just one placeholder to the left of the thousands separator.


Used to display a digit. Each instance of the character represents a position for one number. If no worth exists in a position, Access display screens a zero (0).

Decimal separator

. (period)

Indicates wbelow you desire Access to area the separator character between a whole and decimal component of a number or currency field.

Decimal separators vary and also are set in the neighborhood settings in Windows.

Thousands separator

, (comma)

Indicates where you want Access to place the separator character in between the thousands component of a number or money field.

Thousands separators differ and also are collection in the local settings in Windows.

empty spaces, + - $ ()

Used to insert empty spaces, math personalities (+ -), and also financial icons (¥ £ $) as required anywhere in your format strings. If you want to usage various other common math icons, such as slash ( or /) and also the astehazard (*), surround them through double quotation marks. Keep in mind that you have the right to location them all over.


Used to force Access to screen the character that instantly complies with. This is the exact same as neighboring a character via double quotation marks.


Used to pressure the left alignment of all worths. When you force left alignment, you cannot use the # and 0 digit placeholders, yet you can use placeholders for message personalities.


Used to pressure the character instantly following the asterisk to end up being a fill character — a character used to fill blank spaces. Access generally screens numeric data as right-aligned, and also it fills any kind of location to the left of the worth via blank spaces. You can include fill personalities almost everywhere in a format string, and when you carry out so, Access fills any type of empty spaces through the stated character.

For example, the format £##*~.00 displays a currency amount as £45~~~~~.15. The variety of tilde personalities (~) shown 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 worth by 100 and also display screens the outcome with a trailing percent authorize.

E+, E-


e+, e-

Used to display values in scientific (exponential) notation.

Use this choice as soon as the precharacterized scientific format does not provide enough room for your values. Use E+ or e+ to screen values as positive exponents, and E- or e- to display negative exponents. You must use these placeholders through various other personalities.

For instance, suppose that you use the format 0.000E+00 to a numeric area and also then enter 612345. Access displays 6.123E+05. Access first rounds the variety of decimal areas down to three (the number of zeros to the right or left of the decimal separator). Next, Access calculates the exponent worth from the variety of digits that loss to the best (or left, relying on your language settings) of the decimal separator in the original worth. In this instance, the original value would certainly have actually put "612345" (five digits) to the ideal of the decimal point. For that reason, Access displays 6.123E+05, and also the resulting worth is the identical of 6.123 x 105.

"Literal text"

Use double quotation marks to surround any type of text that you want users to see.

Used to apply a shade to all worths in a section of your format. You have to enclose the name of the shade in brackets and also usage one of these names: babsence, blue, cyan, green, magenta, red, yellow, or white.

Top of Page

Display A.D. or B.C.

You can usage a tradition format to display screen "A.D." prior to or "B.C." after a year relying on whether a positive or negative number is gone into. Optimistic numbers are displayed as years via an "A.D." before the year. Negative numbers are shown as years with a "B.C." after the year.

Open the table in Deauthorize View.

See more: Reckoning With Aggression: The Most Serious Problem With Aggression Is That ?

In the upper area of the design grid, choose the Number area you desire to format.

In the reduced section, click the Format property box, and also then enter this practice format: