locked
Field Calculation Shows Wrong Result due to PC's Locale Settings RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear all,

    I made a simple .dotx template in MS Word 2010 that contains two legacy Text Form Fields. The purpose of field no. 1 is to allow the user to enter an amount, which is then displayed in a uniform format, whereafter field no. 2 will display the same number derived from field no. 1 but multiplied by factor 36. The fields are as follows:

    Field no. 1

    { FORMTEXT }

    Text Form Field Options
    Type: Number
    Default number:
    Maximum length: Unlimited
    Number format: #,##0.00
    Bookmark: Field_no1
    Fill-in enabled: ticked  
    Calculate on exit: ticked

    Field no. 2

    { FORMTEXT { =Field_no1*36 }}

    Text Form Field Options
    Type: Calculation
    Expression: Field_no1*36
    Maximum length: Unlimited
    Number format: #,##0.00
    Bookmark: Field_no2
    Fill-in enabled: unticked  
    Calculate on exit: ticked

    When entering e.g. the amount 12345.89 in Field no. 1 the number is automatically displayed as 12,345.89 and Field no. 2 shows a correctly multiplied value of 444,452.04

    Unfortulately, the above only works properly if one's PC locale is set to English (UK) or similar. If other users try the same with a PC set to locale e.g. Czech (Czech Republic), Field no. 1 still displays a manual entry of 12345.89 correctly as 12,345.89 but the calculation of Field no. 2 suddenly ends wrongly in 364,842.00 

    Using the same template with a PC locale of German (Switzerland) renders yet another result. Field no. 1 still displays the entered value correcty, however calculation of Field no. 2 ends wrongly in 12,884.04

    How can this issue be prevented so that the calculation of Field no. 2 always ends in the correct value no matter what the PC's locale setting?

    PS:  Macros or VBS cannot be used in our organization.
     

    Sunday, December 6, 2015 11:04 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Formfield number formats are locale-dependent. Although 12345.89 might be correctly formatted as 12,345.89 for systems using English-language regional settings, for some systems using English-language regional settings, the correct format would be 12.345,89. One consequence of using a number format in a locale that doesn't support it is that the system with the German (Switzerland) regional setting is summing the 12 and the 345.89 then multiplying that by 36 (12+345.89=357.89, 357.89*36=12,884.04).

    The only non-macro way around this would be to work with whole numbers and no thousand separators (i.e. no number formatting of any kind).

    Cheers
    Paul Edstein
    [MS MVP - Word]


    Sunday, December 6, 2015 8:32 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Formfield number formats are locale-dependent. Although 12345.89 might be correctly formatted as 12,345.89 for systems using English-language regional settings, for some systems using English-language regional settings, the correct format would be 12.345,89. One consequence of using a number format in a locale that doesn't support it is that the system with the German (Switzerland) regional setting is summing the 12 and the 345.89 then multiplying that by 36 (12+345.89=357.89, 357.89*36=12,884.04).

    The only non-macro way around this would be to work with whole numbers and no thousand separators (i.e. no number formatting of any kind).

    Cheers
    Paul Edstein
    [MS MVP - Word]


    Sunday, December 6, 2015 8:32 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Would it work when hidden intermediary fields are inserted (if such a thing exists)?
    Such could e.g. take the value of Field_no1, round it to a full number without aplying number formating, then a next hidden field would take the rounded number and multiply it by 36, before the Filed_no2 is finally using the multiplication result and displays it in an English style number format again. But if so, how could i code such fields?

    Monday, December 7, 2015 12:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You could use separate fields for the integer and decimal components. Formula fields (not a formfield) could then be used to do the math. For example, suppose you have two formfields - Integer and Decimal - for the integer and decimal components, respectively, both of which accept only whole numbers for the input and the Decimal field is limited to two digits. In that case, you could use a pair of formula fields coded as:

    {=INT((Integer*100+Decimal)*36) \# 0}.{=(Integer*100+Decimal)*36-INT((Integer*100+Decimal)*36) \# 00}

    Note the period between the two fields to provide the decimal point. Ordinarly, one could use a MOD function to return the decimal component, but the syntax of that too is region-dependent.

    Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via the standard Word dialogues.

    Cheers
    Paul Edstein
    [MS MVP - Word]


    • Edited by macropodMVP Tuesday, December 8, 2015 6:12 AM
    Tuesday, December 8, 2015 1:28 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Thanks Paul for providing suggestions to users in the forum, really appreciate it.

    Regards,

    Melon Chen
    TechNet Community Support

    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Tuesday, January 12, 2016 8:54 AM