Microsoft Access Foreign Currency Formatting for Text Box

Posted on 2012-08-16
Medium Priority
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
  • 3
  • 2
LVL 52

Expert Comment

by:Gustav Brock
ID: 38303941
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

ID: 38315178

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 52

Accepted Solution

Gustav Brock earned 1500 total points
ID: 38315411
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

ID: 38366370
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 52

Expert Comment

by:Gustav Brock
ID: 38366719
You are welcome.


Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 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