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| Format | Description | Example |
|---|---|---|
| Currency | Displays a number with two decimal places, thousand separators, and the currency sign. | $1,433.20 |
| Fixed | Displays a number with two decimal places. | 1433.20 |
| Standard | Displays a number with two decimal places and the thousands separator. | 1,433.20 |
| Percent | Displays a percent value (a number multiplied by 100 with a percent sign). Displays two digits to the right of the decimal place. | 143320.00% |
| Scientific | Displays a number in scientific notation, with two decimal places. | 1.43E+03 |
| Yes/No | Displays 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 |
Advanced Number Formats
True perfectionists won't be happy with the format options in Table-2. Instead, they'll want complete control over the number of decimal places. One option is to use the FormatCurrency( ), FormatPercent( ), and FormatNumber( ) functions (depending on whether you want the resulting text to use currency format, percent format, or be displayed as an ordinary number). When using these functions, you pass the value that you want to format as the first argument, and the number of decimal places you want to keep in the second argument.
For even more control, you can define a custom number format that spells out exactly what you want, and use that with the Format( ) function. You won't learn about custom number formats, but you can get more information on this feature in Excel 2007: The Missing Manual, or in Access Help.