MS-Access / Getting Started

# Formatting Numbers

Format( ) is one interesting mathematical function, which transforms numbers into text. Format( ) is interesting because the text it creates can be formatted in several different ways, which allows you to control exactly how your numbers are presented.

To understand the difference, think back to the expression you used earlier for discounting product prices:

`SalePrice: [Price] * 0.95`

Even if the Price field has the Currency data type, the calculated values in the SalePrice field appear as ordinary numbers (without the currency sign, thousands separator, and so on). So you see 43.2 when you might prefer \$43.20.

You can remedy this problem by using the Format( ) function to apply a currency format:

`SalePrice: Format([Price] / 0.95, "Currency")`

Now the calculated values include the currency sign. Even better, since currencies are displayed with just two decimal places, you no longer need to clean up fractional values with the Round( ) function.

The trick to using the Format( ) function is knowing what text to supply for the second argument in order to get the result you want. Table-2 spells out your options.

Table-2. Formatting Options
FormatDescriptionExample
CurrencyDisplays a number with two decimal places, thousand separators, and the currency sign.\$1,433.20
FixedDisplays a number with two decimal places.1433.20
StandardDisplays a number with two decimal places and the thousands separator.1,433.20
PercentDisplays a percent value (a number multiplied by 100 with a percent sign). Displays two digits to the right of the decimal place.143320.00%
ScientificDisplays a number in scientific notation, with two decimal places.1.43E+03
Yes/NoDisplays No if the number's 0 and Yes if the number's anything else. You can also use the similar format types True/False and On/Off.Yes