Microsoft Office Tutorials and References
In Depth Information
Access Data Types
Note: When using Fixed, Standard, Percent, or Scientific, you should also set the Decimal Places field
property to the number of decimal places you want to see. Otherwise, you always get two places.
• A custom format string. This is a cryptic code that tells Access exactly how
to format a number. You need to type the format string you need into the
Format box. For example, if you type in the weird-looking code #,##0, (including
the comma at the end) Access hides the last three digits of every number, so
1,000,000 appears as 1,000 and 15,000 as 15.
Note: Custom number formats aren’t terribly common in Access (they’re more frequently used with Excel).
Currency is a slight variation on the Number data type that’s tailored for financial
calculations. Unlike with the Number data type, here you can’t choose a Field Size
for the Currency data type—Access has a one-size-fits-all policy that requires eight
bytes of storage space.
Tip: The Currency data type is better than the Number data type because it uses optimizations that
prevent rounding errors with very small fractions. The Currency data type is accurate to 15 digits to the left
of the decimal point, and 4 digits to the right.
You can adjust the number of decimal places that Access shows for currency values
on the datasheet by setting the Decimal Places field property. Usually, it’s set to 2.
The formatting that Access uses to display currency values is determined by the
“Regional and Language” settings on your computer. However, these settings might
produce results you don’t want—for example, say you run an artisanal cereal
business in Denmark that sells all its products overseas in U.S. dollars (not kroner). You
can control exactly how currency values are formatted by setting the Format field
property, which gives you the following options:
• Currency. This option is the standard choice. It uses the formatting based on
your computer’s regional settings.
• Euro. This option always uses the Euro currency symbol (€).
• A custom format string. This option lets you use any currency symbol you
want (as described below). You need to type the format string you need into the
There’s a simple recipe for cooking up format strings with a custom currency
symbol. Start by adding the character for the currency symbol (type in whatever you
want), and then add #,###.##, which is Access code for “give me a number with
thousands separators and two decimal places.”