The only provisos are that the user must enter values using his own regional settings and if the region is subsequently changed, the values in the fields don't change (unless the macro is run again). If you want a currency format, you have to apply it to each cell yourself. ActiveDocument.Variables("Value1").Value = strValue1 In this case, Field Name is the name of your number or currency field. If you don't want to embed an Excel worksheet in the document, see my, https://www.msofficeforums.com/16726-post2.html, http://ask.brothersoft.com/how-do-i-...le-113359.html. If you want a currency format, you have to apply it to each cell yourself. Save your changes and switch to Datasheet view to see whether the format meets your needs. This works perfectly, but the problem I have is with the currency format of the number. table cells, either as docvariable fields or directly. In the upper section of the design grid, select the Date/Time field that you want to format. To use Reveal Formatting, select the text for which you want to view the formatting information and press Shift+F1. See, for example: Thank you for the help. Up to four sections are possible for a string and each section is separated by a semicolon (;). Each instance of the character represents a position for one number. Ihm folgt in Anführungszeichen die jeweils gewünschte Formatanweisung. For more information, see Propagate a field property. strValue2 = Format(strValue2, "$#,###,##0.00;($#,###,##0.00)") Even if you don’t end up working manually with fields very often, this is not wasted study! Highlight the two columns, and run the macro. I type a number in MS Word and I would like to show it as currency (i.e. Click Shift + F9 to collapse your field. 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. I tried using Format(Value, "$ # ##0.00;($ # ##0.00)") but if my regional settings are not the same as the format above then it adds a decimal where there shouldn't Used to display a digit. So geht’s: Um Zahlen korrekt darzustellen, wird zunächst der Formatschalter \# gesetzt. End With The answer therefore is to perform all the calculations in VBA and write the final values to the Dim strValue2 As String The number of tilde characters (~) displayed in the field depends on the number of blank spaces in the table field. I did something like this for Word 2003. You can specify formats for four types of numeric values — positive, negative, zero (0), and null (undefined). strValue1 = Format(strValue1, "$#,###,##0.00;($#,###,##0.00)") strTotal = Format(strTotal, "$#,###,##0.00;($#,###,##0.00)") There is a work-around, however. You could use a custom format to display "A.D." before or "B.C." However, these steps can be modified for all currency symbols available through the font files installed on your computer. Used to insert blank spaces, math characters (+ -), and financial symbols (¥ £ $) as needed anywhere in your format strings. 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. Doch beim Testen fiel uns schnell auf: Beträge ab 1.000 Euro zeigt Word zwar richtig an, ABER; bei kleineren Beträgen wird jede nicht vorhandene Stelle durch ein Leerzeichen ersetzt. \# „.0,00 €“. Example of custom formatting: #,###.##;(#,###.##)[Red];0,000.00;"Undefined". We’re sorry. Tip You can use the DecimalPlaces property to override the default number of decimal places for the predefined format specified for the Format property. If it suits your purposes you could embed an Excel worksheet in the document. The user may enter the numbers in those text fields using the local n,nnn,nnn.nn or n.nnn.nnn,nn formats and VBA will total them regardless, using the following code to add the three values, You can add fill characters anywhere in a format string, and when you do so, Access fills any blank spaces with the specified character. Der korrekt aufgebaute Satz lautet nun z. . Used to display values in scientific (exponential) notation. Enter values that are longer or shorter than you anticipate (with and without separators), and see how the format behaves. It should look like this “$#,###.00”. The following are examples of custom number formats. - Bloggerin zu Excel und Access auf www.huegemann-informatik.de be one. Used as the last character in a format string. lngVal2 = .TextBox2.Value And I don't usually use the code tags for short things like that because I find it cumbersome and annoying to read. Tap your space bar once, then key in \w and tap your space bar once again. 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. Multiplies the value by 100 and displays the result with a trailing percent sign. oRng.MoveStartWhile "0123456789,. Access displays 6.123E+05. If no value exists in a position, Access displays a zero (0). In this case, Field Name is the name of your Date/Time field. To display zeroes instead of blank spaces. 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. Does the format put the separators in the correct places? Open the form or report Layout View or Design View. The negative value is enclosed within literal characters or parentheses. For example, the format £##*~.00 displays a currency amount as £45~~~~~.15. In the lower section, click the Format property box, and then enter this custom format. However, when we merge some mails, we should pay more attention to the format of date, currency and number. 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. Setting Description; General Number (Default) Display the number as entered. Daher: Excelzwischenliste => Excel formatiert es in Text mit der korrekten Darstellung, damit Word, welches es einfach nicht bringt, nur Text verarbeiten muss. Word tables don't have any automatic cell formats. Used to force Access to display the character that immediately follows. ", wdBackward For example: to display 1234 as 1234.00, use the number 0 as the placeholder like this ####.00. strValue3 = Format(strValue3, "$#,###,##0.00;($#,###,##0.00)") 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. In the Field Properties section, click the arrow in the Format property box, and select a format from the drop-down list. Indicates where you want Access to place the separator character between the thousands part of a number or currency field. Bei den zu Ende gegangenen Office-Kompetenztagen in Fulda erfreuten sich die beiden Seriendruck-Kurse großer Beliebtheit. Visit our UserVoice Page to submit and vote on ideas. For example, suppose that you apply the format 0.000E+00 to a numeric field and then enter 612345. strValue1 = .TextBox1.Value 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. Is there an update on this yet? It is not so hard if you can copy and paste. If the second section contains (#,###.##)[Red]. Manage appointments, plans, budgets — it’s easy with Microsoft 365.. oRng.Fields.Add Range:=oRng, _ For example, I type a number and then press the shortcut keys to format the number as currency (according to the format which has been in set in Settings/Region). You can use a field to insert any number you want in any format you want. after the year. Das klingt alles total toll Display a plus (+) or minus (–) sign with positive or negative numbers; display 0.0 if the value is zero. I would like to format cells in a table in Microsoft word to automatically show as currency. Unfortunately, Word does not allow you to do this type of formatting directly. Negative values with two decimal places, in parentheses and red type. TIPP Set the Format property to one of the predefined date formats. Formatting Cells for Currency and Accounting. Each instance of the character represents a position for one number. Position the pointer in the text box with the number or currency. You might find this helpful if you are working in an area that requires an expression, such as a macro or a query.