Access Textbox Custom Number Format

user_5514747 0
Viewed 272

I am from Bangladesh and in Bangladesh comma (,) is used as thousand separator. We use comma after 3 digit, 5 digit, 7 digit from right to left like 9,999, 99,999, 9,99,999,99,99,999,9,99,99,999 & 99,99,99,999. I was trying to accomplish this format by textbox format property. When I use #,##0 as format then it only format till 5 digit. When number is 6 digit or higher then it only shows one comma like 456,456 while expected is 4,56,456. I have tried to use #,##,##,##0 but it automatically goes to #,##0. So, how can I format the text box to get my desired result as below?

enter image description here

enter image description here

ms-access
1 Answers

You can't. You'll have to run a custom format like this:

TextValue = Format(Fix(Value), Left("##\,##\,##\,##\,##\,##\,", -Int(-(Len(Abs(Fix(Value))) - 2) \ 2) * 4) & "##0")

Note, the negative values will be formatted correctly as well while decimals will be cut off.

If you have decimals, append these:

TextValue = Format(Fix(Value), Left("##\,##\,##\,##\,##\,##\,", -Int(-(Len(Abs(Fix(Value))) - 2) \ 2) * 4) & "##0") & LTrim(Str(Abs(CCur(Value)-Fix(Value))))

As used as ControlSource (read-only) in a form or a report:

=Format(Fix([Amount]),Left("##\,##\,##\,##\,##\,##\,",-Int(-(Len(Abs(Fix([Amount])))-2)\2)*4) & "##0")

Addendum:

To cover any situation with values:

  1. Larger than 1, with or without decimals
  2. Smaller than 1, a positive decimal value
  3. Zero
  4. Larger than -1, a negative decimal value
  5. Smaller than -1, with or without decimals

an extended expression is needed:

TextValue = Format(Value, ";-") & _
    Format(Abs(Fix(Value)), Left("##\,##\,##\,##\,##\,##\,", -Int(-(Len(CStr(Abs(Fix(Value)))) - 2) \ 2) * 4) & "##0") & _
    IIf(Value - Fix(Value), LTrim(Str(Abs(Value - Fix(Value)))), "")
  1. First part controls the sign
  2. Second part controls the integer value
  3. Third part controls decimals

This will output correctly for any value within the entire range of Currency.

user_35272970
Related