CODEWITHSUNDEEP
ms-access
Harun24hr
Harun24hr

Reputation: 36840

Access Textbox Custom Number Format

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

Upvotes: 1

Views: 1404

Answers (1)

Gustav
Gustav

Reputation: 55816

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.

Upvotes: 1

Related Questions