what format to use on access for finances
The Number (including Large Number) and Currency Data Types have a wide variety of formats to help meet your unique circumstances. With number and currency formatting you accept three choices: keep the default formats, utilise a predefined format, or create a custom format. When y'all apply a format to a table field, that aforementioned format is automatically practical to whatever form or report control that you subsequently bind to that table field. Formatting only changes how the information is displayed and does non bear on how the data is stored or how users enter data.
In this article
-
Overview of number and currency formats
-
Apply a predefined format
-
Examples of predefined formats
-
Apply a custom format
-
Examples of custom formats
-
Custom format characters
-
Brandish A.D. or B.C.
Overview of number and currency formats
To create a custom format, you lot'll enter various characters in the Format belongings of a table field. The characters can exist placeholders (such as 0 and #), separators (such equally periods and commas), literal characters, and colors based on how you want the formatting to be. Just retrieve that Access automatically applies whatever custom formats that y'all use in a table field, to a control in a course or report if it is bound (linked).
You lot 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 goose egg values third, and the format for zip values terminal. Also, you lot must separate each format with a semicolon.
Instance of custom formatting: #,###.##;(#,###.##)[Cerise];0,000.00;"Undefined"
Displays every bit: i,234.568-1.234.568 = 0
| Here'southward what the formatting ways |
|
Up to four sections are possible for a cord and each section is separated by a semicolon (;). If your table field does accept null values, yous tin can omit the 4th department.
| Section | Format Description | Example |
| If the showtime section contains #,###.## | Displays positive values. | 1234.5678 displays equally i,234.568 This format uses the comma every bit the thousands separator and the catamenia equally 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 case, if your field contains iii,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 than placeholders for the decimal value, such every bit #,###.###. |
| If the second section contains (#,###.##)[Ruddy] | Displays only negative values. | The negative value is enclosed within literal characters or parentheses. In this instance, any negative value will be displayed in cherry color. |
| If the tertiary 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 4th section contains "Undefined" | Defines what users run across when a tape contains a zip value. In this case, users run across the word "Undefined." | You lot can also use other text, such every bit "Null" or "****". Surrounding characters with double quotation marks are treated as literals and are displayed exactly as entered. |
Top of Page
Use a predefined format
Access provides several predefined formats for number and currency data. The default format is to brandish 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 belongings.
In a tabular array
-
Open the table in Pattern View.
-
In the upper section of the pattern grid, select the Appointment/Time field that you desire to format.
-
In the Field Backdrop section, click the arrow in the Format property box, and select a format from the drop-down listing.
-
After y'all select a format, the Property Update Options button appears, and lets you to apply your new format to whatever other table fields and grade controls that would logically inherit information technology. 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 proper noun 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 come across whether the format meets your needs.
Notation New forms, reports, or views that y'all create based on this tabular array get the table's formatting, just you can override this on the class, study, or view without changing the tabular array'south formatting.
In a grade or written 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 course or report Layout View or Blueprint View.
-
Position the pointer in the text box with the number or currency.
-
Press F4 to brandish the Property Sheet.
-
Set the Format property to one of the predefined date formats.
In a query
-
Open up the query in Pattern View.
-
Right-click the date field, and then click Backdrop.
-
In the Property Sheet, select the format you want from the Format property listing.
In an expression
-
Use the FormatDateTime office to format a date value into ane of several predefined formats.
You might detect this helpful if you are working in an area that requires an expression, such as a macro or a query.
Superlative of Page
Examples of predefined formats
The post-obit tabular array shows the predefined Format holding settings for numbers and currency.
| Setting | Description |
| General Number | (Default) Display the number every bit entered. |
| Currency | Utilise the k 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 g separator; follow the settings specified in the regional settings of Windows for negative amounts, decimal symbols, and decimal places. |
| Percentage | Multiply the value by 100 and append a percentage sign (%); follow the settings specified in the regional settings of Windows for negative amounts, decimal symbols, and decimal places. |
| Scientific | Use standard scientific annotation. |
), regardless of the currency symbol specified in the regional settings of Windows.The following are examples of the predefined number formats.
| Setting | Data | Display |
| General Number | 3456.789 | 3456.789 |
| Currency | 3456.789 | $3,456.79 |
| Fixed | 3456.789 | 3456.79 |
| Standard | 3456.789 | 3,456.79 |
| Percent | iii | 300% |
| Scientific | 3456.789 | 3.46E+03 |
Summit of Page
Apply a custom format
-
Open the table in Design View.
-
In the upper section of the design grid, select the Date/Time field that y'all want to format.
-
In the Field Backdrop department, select the Full general tab, click the prison cell adjacent to the Format box and enter the specific characters based on your formatting needs.
-
Later on you select a format, the Property Update Options smart tag appears (Access 2010 only), and lets you to apply your new format to whatsoever other table fields and course controls that would logically inherit it. To utilise your changes throughout the database, click the smart tag, and then click Update Format everywhere Field Proper name is used. In this example, Field Proper name is the name of your Appointment/Time field.
-
To employ 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 Yeah.
For more information, come across Propagate a field property.
-
Save your changes and switch to Datasheet view to meet 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 right 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 zippo or a aught value in a format meant for positive or negative values, and run across whether y'all like the result.
-
Note When you utilize a format to a table field, Access uses that same format in any course or report controls that yous demark (link) to that field.
Top of Folio
Examples of custom formats
The post-obit are examples of custom number formats.
| Setting | Description |
| 0;(0);;"Zippo" | Brandish positive values usually; display negative values in parentheses; display the word "Null" if the value is Naught. |
| +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. |
Elevation of Page
Custom format characters
To create a custom format, apply the following characters every bit placeholders and separators.
| Character | Description |
| # | Used to display a digit. Each instance of the character represents a position for 1 number. If no value exists in a position, Admission displays a blank space. Likewise, can be used equally a placeholder. For example, if you lot use the format # ,# ## and enter a value of 45 in the field, 45 is displayed. If y'all enter 12,145 in a field, Admission displays 12,145 — even though you defined but one placeholder to the left of the thousands separator. |
| 0 | Used to brandish a digit. Each instance of the character represents a position for 1 number. If no value exists in a position, Access displays a zero (0). |
| Decimal separator . (period) | Indicates where you desire Admission to place the separator character betwixt a whole and decimal role of a number or currency field. Decimal separators vary and are set in the regional settings in Windows. |
| Thousands separator , (comma) | Indicates where you desire Admission 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. |
| bare spaces, + - $ () | Used to insert blank spaces, math characters (+ -), and financial symbols (¥ £ $) every bit needed anywhere in your format strings. If you lot want to employ other common math symbols, such as slash (\ or /) and the asterisk (*), environs them with double quotation marks. Annotation that you lot tin place them anywhere. |
| \ | Used to force Admission to display the character that immediately follows. This is the aforementioned equally surrounding a graphic symbol with double quotation marks. |
| ! | Used to forcefulness the left alignment of all values. When you lot force left alignment, y'all cannot utilize the # and 0 digit placeholders, only you can utilize 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. Admission usually displays numeric data as right-aligned, and it fills whatsoever area to the left of the value with blank spaces. Yous tin can add fill characters anywhere in a format string, and when you exercise so, Admission fills whatsoever 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 equally the final grapheme in a format cord. Multiplies the value past 100 and displays the result with a trailing percentage sign. |
| East+, E- –or– east+, e- | Used to display values in scientific (exponential) annotation. Apply this option when the predefined scientific format doesn't provide sufficient room for your values. Utilize E+ or east+ to brandish values as positive exponents, and Eastward- or e- to display negative exponents. You must use these placeholders with other characters. For example, suppose that you employ 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). Adjacent, Access calculates the exponent value from the number of digits that fall to the correct (or left, depending on your linguistic communication settings) of the decimal separator in the original value. In this case, the original value would have put "612345" (v digits) to the right of the decimal point. For that reason, Admission displays 6.123E+05, and the resulting value is the equivalent of half-dozen.123 10 105. |
| "Literal text" | Use double quotation marks to surround any text that you desire users to see. |
| [color] | Used to use a colour 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, greenish, magenta, red, yellow, or white. |
Top of Page
Display A.D. or B.C.
You could use a custom format to brandish "A.D." before or "B.C." after a year depending on whether a positive or negative number is entered. Positive numbers are displayed every bit years with an "A.D." before the yr. Negative numbers are displayed as years with a "B.C." subsequently the year.
-
Open the table in Design View.
-
In the upper section of the design filigree, select the Number field yous desire to format.
-
In the lower section, click the Format property box, and then enter this custom format:
"A.D. " #;# " B.C."
Source: https://support.microsoft.com/en-us/office/format-a-number-or-currency-field-e48f2312-67f0-4921-aca0-15d36b7f9c3b
Belum ada Komentar untuk "what format to use on access for finances"
Posting Komentar