Microsoft Access Foreign Currency Formatting for Text Box

Posted on 2012-08-16
Last Modified: 2012-09-04
We have developed a multi currency solution for our MSACCESS application that uses a 'CurrencyCodes' table loaded with all the currencies required to be supported by our software.  This includes:-

ISO Currency Code (i.e. AUD, EUR, GBP, KRW etc..)
Currency Symbol (i.e $, €, £,¿ etc..)
Currency Decimal Symbol (i.e. ' or , or / or - etc..)
Currency Digit Grouping Seperator (i.e. , or . or space)
Currency Digits after Separator (ie. 0,2,3)
Currency Digit Grouping (i.e. 3;0 or 3;2)
Currency Positive Pattern
Currency Negative Pattern

We have code developed to build up a currency 'Format String' for the required currency that is set under code to each 'Currency' field on a form at startup.  We specifically do not use the Windows Regional Settings (LOCALE) of the user.  

ie. txtCurrencyField.Format=strCurrencyFormatString

This approach all works well except for a number of scenarios including:-

1. Digit Grouping Symbol of " " (Space)
2. Digit Grouping 3;2
3. Some Unicode Currency Symbol just don't format correctly (i.e. ¿.¿. or  ¿.¿.)

For examples of problem:-

Example 1
ISO Code NOK (Norway, Krone) has 'Decimal Symbol = , " and 'Grouping Seperator = Space" so format string we build under code is:-

kr # ##0,00;kr -# ##0,00

When assigned to 'Format' property this displays as:-

kr 123,4 56,790  whereas should be kr 123 456 789,52

Example 2

ISO Code INR (India, Rupees) has 'Decimal Symbol = ." and 'Grouping Seperator = ," and 'Digit Grouping = 3;2" so format string we build under code is:-

R\s. ##,##,##,##0.00;R\s. -##,##,##,##0.00

When assigned to 'Format' property this displays as:-

Rs123456789. 520000000.00  wheras should be Rs. 12,34,56,789.52

NOTE:- If remove . from "Rs." to "Rs" it displays as follows:-

Rs 123,456,789.52 wheras should be Rs. 12,34,56,789.52

The digit grouping just is not enforced.

Example 3

ISO Code AED (UAE Dirham) has 'Decimal Symbol = ." and 'Grouping Seperator = ," and 'Digit Grouping = 3" and 'Currency Symbol = ¿.¿.' so format styring we build under code is:-

?.?. #,##0.00;?.?.#,##0.00-

This displays as:-

¿123456789.¿. 5200.00

Whereas should be ¿.¿.¿ 123,456,789.52


- How to format currency strings where digit grouping seperator is BLANK (single space)
- How to format currency digit groupings of "3;2" (for India, Rupees).
- How to handle complex/unicode currency symbols such as AED (UAE Dirham)

I am in Australia and my current Regional Settings (LOCALE) are to use a $ currency symbol, 2 decimal places, comma (,) seperator and digit grouping of 3;0.  Whatever solution is proposed should not look to change my current regional settings.  The solution needs to be dynamic by currency code.

Hope this is enough info to assist in proposing solutions.

Question by:wghayes
    LVL 48

    Expert Comment

    by:Gustav Brock
    Your problem is that - to Format - dot and comma (and slash for dates) are the placeholders for the decimal and thousands (and date) separators. When processed, these will be substituted with whatever the current Windows settings dictate.

    For example, here we use comma and dot as the separators, thus I can format your Norwegian string:

    ? Format(1234567.89, "kr ### ### ##0.00;kr -### ### ##0.00")
    and get:
    kr 1 234 567,89
    but for smaller amounts you will have to use:

    ? LTrim(Format(4567.89, "kr ### ### ##0.00;kr -### ### ##0.00"))
    and that would fail for negative values.

    Thus, your only solution is to run your own custom format function where you (the user) select the locale and then build the string.

    Your lifesaver here will be Str which _always_ converts a decimal value to a plain string having a dot as the decimal separator and one leading char which is a space for positive values and a minus sign for negative values.
    Then you safely can use Split to separate the integer and the decimal:

    If curValue = Int(curValue) Then
      strInt = Str(curValue)
      strDec = ""
      strInt = Split(Str(curValue), ".")(0)
      strDec = Split(Str(curValue), ".")(1)

    Now you can expand strInt with thousand separators as needed. Then:

      strValue = strInt & strDecimalSeparator & strDec

    or, to strip a leading space:

      strValue = LTrim(strInt) & strDecimalSeparator & strDec


    Author Comment


    Thanks for the feeback on a possible solution for the currency formatting.

    The solution you have proposed will work where we wish to manually create a 'Formatted' text/string result based on passing in a currency value.  This would be useful for a REPORT or UNBOUND control on a form where it is manully handled under code, however, does not answer the question asked.  

    The question asked was is it possible to do the same via FORMAT property of a control in MSACCESS?  

    i.e.  txtCurrencyField.Format=strCurrencyFormatString

    I have trialled setting the 'ControlSource' property to a function call to return a formatted value and whilst this works okay for existing BOUND controls it does not allow any value to be entered to the field. (i.e. error 'control can't be edited; it's bound to the expression '...')

    e.g. txtCurrencyBOUND.controlsource=pFormatCurrencyEx(StandardCostRate,2,3,",","$",".","3",0,1)

    Can you please comment specifically on the format property approach.  Is it at all possible?

    LVL 48

    Accepted Solution

    Well, the answer was that it is not possible because Format uses the Windows settings and misses a parameter for culture or localisation.

    It is, or course, correct that a control bound to an expression is not updatable.

    To update a value, you can have an unbound textbox which you fill OnCurrent or OnEnter with the value of StandardCostRate.
    At BeforeUpdate of this, update a (hidden) control bound to StandardCostRate. If the value was formatted, parse it first, then update.
    At AfterUpdate fill it with the formatted value stored in StandardCostRate. Block for a second update of the control.

    Quite convoluted but doable.


    Author Closing Comment

    The response to the question was to essentially advise what I wanted to do was not possible using the FORMAT property in MSACCESS.  However, the proposed work around provided some way forward and on that basis I accept the solution offerred.

    LVL 48

    Expert Comment

    by:Gustav Brock
    You are welcome.


    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now