Table of contents
TOC
Collapse the table of content
Expand the table of content

Format Property - Number and Currency Data Types

office 365 dev account|Last Updated: 4/3/2018
|
3 Contributors

Applies to: Access 2013 | Access 2016

In this articleSettingRemarksExamples

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

Setting

Predefined Formats

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

SettingDescription
General Number(Default) Display the number as entered.
CurrencyUse the thousand separator; follow the settings specified in the regional settings of Windows for negative amounts, decimal and currency symbols, and decimal places.
EuroUse the euro symbol (Euro symbol), regardless of the currency symbol specified in the regional settings of Windows.
FixedDisplay at least one digit; follow the settings specified in the regional settings of Windows for negative amounts, decimal and currency symbols, and decimal places.
StandardUse the thousand separator; follow the settings specified in the regional settings of Windows for negative amounts, decimal symbols, and decimal places.
PercentMultiply 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.
ScientificUse standard scientific notation.

Custom Formats

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.

SectionDescription
FirstThe format for positive numbers.
SecondThe format for negative numbers.
ThirdThe format for zero values.
FourthThe 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 create custom number formats by using the following symbols.

SymbolDescription
. (period)Decimal separator. Separators are set in the regional settings in Windows.
, (comma)Thousand separator.
0Digit placeholder. Display a digit or 0.
#Digit placeholder. Display a digit or nothing.
$Display the literal character "$".
%Percentage. The value is multiplied by 100 and a percent sign is appended.
E- or e-Scientific notation with a minus sign (-) next to negative exponents and nothing next to positive exponents. This symbol must be used with other symbols, as in 0.00E-00 or 0.00E00.
E+ or e+Scientific notation with a minus sign (-) next to negative exponents and a plus sign (+) next to positive exponents. This symbol must be used with other symbols, as in 0.00E+00.

Remarks

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.

SettingDataDisplay
General Number3456.789 -3456.789 $213.213456.789 -3456.789 $213.21
Currency3456.789 -3456.789$3,456.79 ($3,456.79)
Fixed3456.789 -3456.789 3.566453456.79 -3456.79 3.57
Standard3456.7893,456.79
Percent3 0.45300% 45%
Scientific3456.789 -3456.7893.46E+03 -3.46E+03

The following are examples of custom number formats.

SettingDescription
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.0Display a plus (+) or minus (-) sign with positive or negative numbers; display 0.0 if the value is zero.

ACCESS SUPPORT RESOURCES
Access for developers forum on MSDN
Access help on support.office.com
Access help on answers.microsoft.com
Search for specific Access error codes on Bing
Access forums on UtterAccess
Access wiki on UtterAcess
Access developer and VBA programming help center (FMS)
Access posts on StackOverflow

© 2018 Microsoft