asked on

Microsoft Access Foreign Currency Formatting for Text Box

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

Questions:-

- 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.

Thanks,
Wayne.

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 = ""
Else
strInt = Split(Str(curValue), ".")(0)
strDec = Split(Str(curValue), ".")(1)
EndIf

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

/gustav

Wayne Hayes

ASKER

Gustav,

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?

Thanks,
Wayne.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wayne Hayes

ASKER

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.

Cheers,
Wayne.

Gustav Brock

You are welcome.

/gustav