The Number (including Large Number) and Currency Data Types in Access have a wide variety of formats to help meet your unique circumstances. With number and currency formatting you have three choices: keep the default formats, apply a predefined format, or create a custom format. When you apply a format to a table field, that same format is automatically applied to any form or report control that you subsequently bind to that table field. Formatting only changes how the data is displayed and does not affect how the data is stored or how users enter data.
In this article
Overview of number and currency formats
To create a custom format, you’ll enter various characters in the Format property of a table field. The characters can be placeholders (such as 0 and #), separators (such as periods and commas), literal characters, and colors based on how you want the formatting to be. Just remember that Access automatically applies any custom formats that you use in a table field, to a control in a form or report if it is bound (linked).
You can specify formats for four types of numeric values — positive, negative, zero (0), and null (undefined). If you choose to create a format for each type of value, you must put the format for positive values first, the format for negative values second, the format for zero values third, and the format for null values last. Also, you must separate each format with a semicolon.
Example of custom formatting: #,###.##;(#,###.##)[Red];0,000.00;"Undefined"
Displays as: 1,234.568-1.234.568 = 0
|
Here’s what the formatting means |
|
Up to four sections are possible for a string and each section is separated by a semicolon (;). If your table field does accept null values, you can omit the fourth section.
|
Section |
Format Description |
Example |
|---|---|---|
|
If the first section contains #,###.## |
Displays positive values. |
1234.5678 displays as 1,234.568 This format uses the comma as the thousands separator and the period as the decimal separator. If the decimal values in the record exceed the number of placeholder characters in the custom format, Access rounds the values and displays only the number of values specified by the format. For example, if your field contains 3,456.789, but its format specifies two decimal places, Access rounds the decimal value to 0.79. Tip: For a format with larger values or more decimal places, add more placeholders for the decimal value, such as #,###.###. |
|
If the second section contains (#,###.##)[Red] |
Displays only negative values. If your data does not contain negative values, Access leaves the field blank. |
The negative value is enclosed within literal characters or parentheses. In this example, any negative value will be displayed in red color. |
|
If the third section contains 0,000.00 |
Defines the format for all zero (0) values. |
When the field contains a value of zero, 0,000.00 is displayed. To display text instead of a number, use "Zero" (surrounded by double quotation marks). |
|
If the fourth section contains "Undefined" |
Defines what users see when a record contains a null value. In this case, users see the word "Undefined." |
You can also use other text, such as "Null" or "****". Surrounding characters with double quotation marks are treated as literals and are displayed exactly as entered. |
Apply a predefined format
Access provides several predefined formats for number and currency data. The default format is to display the number as entered.
Tip You can use the DecimalPlaces property to override the default number of decimal places for the predefined format specified for the Format property.
In a table
-
Open the table in Design View.
-
In the upper section of the design grid, select the Date/Time field that you want to format.
-
In the Field Properties section in the lower section, click the arrow in the Format property box, and select a format from the drop-down list.
-
After you select a format, the Property Update Options button appears, and lets you to apply your new format to any other table fields and form controls that would logically inherit it. To apply your changes throughout the database, click the smart tag, and then click Update Format everywhere <Field Name> is used. In this case, Field Name is the name of your number or currency field.
-
To apply your changes to the entire database, when the Update Properties dialog box appears and displays the forms and other objects that will inherit the new format. Click Yes.
For more information, see Propagate a field property.
-
Save your changes and switch to Datasheet view to see whether the format meets your needs.
Note New forms, reports, or views that you create based on this table get the table’s formatting, but you can override this on the form, report, or view without changing the table’s formatting.
In a form or report
In a form or report, dates are usually shown in text boxes. Just set the Format property for the text box to the date format you want.
-
Open the form or report Layout View or Design View.
-
Position the pointer in the text box with the number or currency.
-
Press F4 to display the Property Sheet.
-
Set the Format property to one of the predefined date formats.
In a query
-
Open the query in Design View.
-
Right-click the date field, and then click Properties.
-
In the Property Sheet, select the format you want from the Format property list.
In an expression
-
Use the FormatDateTime function to format a date value into one of several predefined formats.
You might find this helpful if you are working in an area that requires an expression, such as a macro or a query.
Examples of predefined formats
The following table shows the predefined Format property settings for numbers and currency.
|
Setting |
Description |
|---|---|
|
General Number |
(Default) Display the number as entered. |
|
Currency |
Use the thousand separator; follow the settings specified in the regional settings of Windows for negative amounts, decimal and currency symbols, and decimal places. |
|
Euro |
Use the euro symbol ( |
|
Fixed |
Display at least one digit; follow the settings specified in the regional settings of Windows for negative amounts, decimal and currency symbols, and decimal places. |
|
Standard |
Use the thousand separator; follow the settings specified in the regional settings of Windows for negative amounts, decimal symbols, and decimal places. |
|
Percent |
Multiply 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. |
|
Scientific |
Use standard scientific notation. |
The following are examples of the predefined number formats.
|
Setting |
Data |
Display |
|---|---|---|
|
General Number |
3456.789 –3456.789 $213.21 |
3456.789 –3456.789 $213.21 |
|
Currency |
3456.789 –3456.789 |
$3,456.79 ($3,456.79) |
|
Fixed |
3456.789 –3456.789 3.56645 |
3456.79 –3456.79 3.57 |
|
Standard |
3456.789 |
3,456.79 |
|
Percent |
3 0.45 |
300% 45% |
|
Scientific |
3456.789 –3456.789 |
3.46E+03 –3.46E+03 |
Apply a custom format
-
Open the table in Design View.
-
In the upper section of the design grid, select the Date/Time field that you want to format.
-
In the Field Properties section, select the General tab, click the cell next to the Format box and enter the specific characters based on your formatting needs.
-
After you select a format, the Property Update Options smart tag appears, and lets you to apply your new format to any other table fields and form controls that would logically inherit it. To apply your changes throughout the database, click the smart tag, and then click Update Format everywhere Field Name is used. In this case, Field Name is the name of your Date/Time field.
-
To apply your changes to the entire database, when the Update Properties dialog box appears and displays the forms and other objects that will inherit the new format. Click Yes.
For more information, see Propagate a field property.
-
Save your changes and switch to Datasheet view to see whether the format meets your needs.
-
Test the format by doing the following:
-
Enter values without thousands separators or decimal separators, and see how the format treats the data. Does the format put the separators in the correct places?
-
Enter values that are longer or shorter than you anticipate (with and without separators), and see how the format behaves. Does the format add either unwanted blank spaces or leading or trailing zeroes?
-
Enter a zero or a null value in a format meant for positive or negative values, and see whether you like the result.
-
Note When you apply a format to a table field, Access uses that same format in any form or report controls that you bind (link) to that field.
Examples of custom formats
The following are examples of custom number formats.
|
Setting |
Description |
|---|---|
|
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.0 |
Display a plus (+) or minus (–) sign with positive or negative numbers; display 0.0 if the value is zero. |
Custom format characters
To create a custom format, use the following characters as placeholders and separators.
|
Character |
Description |
|---|---|
|
# |
Used to display a digit. Each instance of the character represents a position for one number. If no value exists in a position, Access displays a blank space. Also, can be used as a placeholder. For example, if you apply the format #,### and enter a value of 45 in the field, 45 is displayed. If you enter 12,145 in a field, Access displays 12,145 — even though you defined only one placeholder to the left of the thousands separator. |
|
0 |
Used to display a digit. Each instance of the character represents a position for one number. If no value exists in a position, Access displays a zero (0). |
|
Decimal separator . (period) |
Indicates where you want Access to place the separator character between a whole and decimal part of a number or currency field. Decimal separators vary and are set in the regional settings in Windows. |
|
Thousands separator , (comma) |
Indicates where you want Access to place the separator character between the thousands part of a number or currency field. Thousands separators vary and are set in the regional settings in Windows. |
|
blank spaces, + - $ () |
Used to insert blank spaces, math characters (+ -), and financial symbols (¥ £ $) as needed anywhere in your format strings. If you want to use other common math symbols, such as slash (\ or /) and the asterisk (*), surround them with double quotation marks. Note that you can place them anywhere. |
|
\ |
Used to force Access to display the character that immediately follows. This is the same as surrounding a character with double quotation marks. |
|
! |
Used to force the left alignment of all values. When you force left alignment, you cannot use the # and 0 digit placeholders, but you can use placeholders for text characters. |
|
* |
Used to force the character immediately following the asterisk to become a fill character — a character used to fill blank spaces. Access normally displays numeric data as right-aligned, and it fills any area to the left of the value with blank spaces. You can add fill characters anywhere in a format string, and when you do so, Access fills any blank spaces with the specified character. For example, the format £##*~.00 displays a currency amount as £45~~~~~.15. The number of tilde characters (~) displayed 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 value by 100 and displays the result with a trailing percent sign. |
|
E+, E- –or– e+, e- |
Used to display values in scientific (exponential) notation. Use this option when the predefined scientific format doesn't provide sufficient room for your values. Use E+ or e+ to display values as positive exponents, and E- or e- to display negative exponents. You must use these placeholders with other characters. For example, suppose that you apply the format 0.000E+00 to a numeric field and then enter 612345. Access displays 6.123E+05. Access first rounds the number of decimal places down to three (the number of zeros to the right or left of the decimal separator). Next, Access calculates the exponent value from the number of digits that fall to the right (or left, depending on your language settings) of the decimal separator in the original value. In this case, the original value would have put "612345" (five digits) to the right of the decimal point. For that reason, Access displays 6.123E+05, and the resulting value is the equivalent of 6.123 x 105. |
|
"Literal text" |
Use double quotation marks to surround any text that you want users to see. |
|
[color] |
Used to apply a color to all values in a section of your format. You must enclose the name of the color in brackets and use one of these names: black, blue, cyan, green, magenta, red, yellow, or white. |
Display A.D. or B.C.
You could use a custom format to display "A.D." before or "B.C." after a year depending on whether a positive or negative number is entered. Positive numbers are displayed as years with an "A.D." before the year. Negative numbers are displayed as years with a "B.C." after the year.
-
Open the table in Design View.
-
In the upper section of the design grid, select the Number field you want to format.
-
In the lower section, click the Format property box, and then enter this custom format:
"A.D. " #;# " B.C."
