[RESOLVED] format currency to Indian format in Label/Textbox

[VBFnewcomer]

Hi folks,
I am trying format the currency data in DB (Acs 2003) into Indian currency format 00,00,00,000 for which I used the below code

Me.lblAmt.Caption = Format(CCur(rsCurrent.Fields("suitAmount")), "##,##,##,##,##,##0")

but during display nothing happens i.e. the display continues to be the US format.
Can somebody show me how.
Thanks in advance.

[amrita]

Use format currency

FormatCurrency("123456789", , , , vbTrue)

[shakti5385]

http://www.vbforums.com/showpost.php...30&postcount=6

[VBFnewcomer]

As advised by amrita I changed my code to this
Me.txtAmt.Text = FormatCurrency(rsCurrent.Fields("suitAmount"), 0, 0, vbFalse, vbTrue)
I went to Control Panel and set the currency grouping digits to 12,34,56,789 which was available as an option. Apply & Ok. Then I ran the code....there is hardly any change...

[VBFnewcomer]

Another thing, when I read the documentation for this function I found this

Note All settings information comes from the Regional Settings Currency tab, except leading zero which comes from the Number tab.

at the last few lines. Would the proj fail if the user has not set (i.e. left it at default) or set the Regional settings to a diff format (assuming the function works)....

[amrita]

What is the value of rsCurrent.Fields("suitAmount") ??
Formatcurrency works fine for me.

[VBFnewcomer]

What is the value of rsCurrent.Fields("suitAmount") ??

as of data available today the the value is in few lakhs

[VBFnewcomer]

one more thing the suitAmout is a currency field having no spl formatting..

[amrita]

Test this

Dim curr As Currency
curr = 12345678
MsgBox FormatCurrency(curr, 0, 0, vbFalse, vbTrue)

or set the format in access table design view

[leinad31]

Option Explicit

Public Function FormatIndian(ByVal Amount As Currency) As String   
Dim strAmount As String
Dim strGrpsArr() As String
Dim lngPos As Long
Dim lngIndex As Long

   strAmount = Format$(Amount, "#") 'note: rounding occurs
   If Len(strAmount) < 4 Then
      FormatIndian = strAmount
   Else
      lngIndex = (Len(strAmount) - 2) \ 2
      ReDim strGrpsArr(lngIndex)
      
      strGrpsArr(lngIndex) = Mid$(strAmount, Len(strAmount) - 2)  'least significant trio
      
      lngPos = Len(strAmount) - 4:  lngIndex = lngIndex - 1       'init position variables
      Do
         strGrpsArr(lngIndex) = Mid$(strAmount, lngPos, 2)
         lngPos = lngPos - 2:       lngIndex = lngIndex - 1       'update variables
         If lngPos = 0 Then strGrpsArr(0) = Left$(strAmount, 1)   'leading non-pair group
      Loop Until lngPos <= 0
      
      FormatIndian = Join(strGrpsArr, ",")
      Erase strGrpsArr
   End If
End Function

Private Sub Form_Load()
Dim curTest As Currency

   curTest = 4456456456716#
   Debug.Print FormatIndian(curTest)
End Sub

Me.lblAmt.Caption = FormatIndian(rsCurrent.Fields("suitAmount").Value)

[VBFnewcomer]

Tried all:
1.formatnumber
2.formatcurrency
3.formatting in table design

4.Dim curr As Currency
curr = 12345678
MsgBox FormatCurrency(curr, 0, 0, vbFalse, vbTrue)
sorry...all fails?!
we need to approach it some other way..I suppose

[leinad31]

Have you tried the code in post #10?

[VBFnewcomer]

it works .. it works... thank you Mr. leinad31 that's great. Notably, the code works irrespective of currency settings in control panel setting. Iam trying to comprehend your code after which I will RESOLVED the post.
Thanks also for Ms. Amrita, Mr. shakti5385, Doogle for helping in discovering format functions hitherto unknown to me.
However what made the formatcurrency and formatnumber fail....there certainly should be some logical reason!
Thanks again folks.

[leinad31]

The key parts are...

lngIndex = (Len(strAmount) - 2) \ 2
- used to setup the upper bound of the string array. Another perspective, determines the number of commas to be inserted in return string since array elements are delimited by commas during Join(), eg. lngIndex = 1 means there's one comma.
- consider 6 digits, due to existence of trio group for least significant digits 6\2 is incorrect since you'll get 3 commas instead of just two eg. 9,99,999. The subtraction (-2) is the adjustment in order to return the correct number of commas, eg. (6-2)\2 = 2 for 6 digits, (5-2)\2= 1 for 5 digits.

ReDim strGrpsArr(lngIndex)
- we will use a single dimension string array to hold the digit groups so we can use Join() to build the formatted number.
- array can now be resized since number of digit groups is known, each array element is one group. Since result string of Join() starts with strGrpsArr(0), most significant group should be at array index zero (array lbound) and least significant group (the trio) at array ubound.
- we extract the digits from least significant to most significant since we want to extract them in groups and since lngIndex is currently at ubound anyway.

strGrpsArr(lngIndex) = Mid$(strAmount, Len(strAmount) - 2)
- iteration is for extracting groups of two digits, eg. strGrpsArr(lngIndex) = Mid$(strAmount, lngPos, 2), so we can't process the trio in the iteration. Since lngIndex currently equals array ubound and since the least significant group is assigned at array ubound, we process the trio before the iteration then position lngPos accordingly for start of iteration.

If lngPos = 0 Then strGrpsArr(0) = Left$(strAmount, 1)
- if lngPos = 2 before lngPos = lngPos - 2 then most significant group has one digit (at lngPos = 1)... So if lngPos = 0 (after lngPos decrement) we handle the remaining digit with Left$(). Also lngPos = 0 is one indication that processing is finished (all digits processed).
- if most significant group has two digits then you'll arrive at lngPos = 1 after successive decrements. Decrementing again by 2 sets lngPos = -1, this is another indication that processing is finished.
- another alternative to Loop Until lngPos <= 0 is Loop Until lngPos < 1.

FormatIndian = Join(strGrpsArr, ",")
- build the string, array elements delimited with comma.

EDIT:
It's all regional setting independent right now cause were dealing with unformatted whole numbers or were not handling the separator characters (we stripped them with Format( , "#"). If there are decimals, then we have to determine first what the character is for the decimal separator which is dependent on regional settings.

[vinayak.v]

Option Explicit

Public Function FormatIndian(ByVal Amount As Currency) As String   
Dim strAmount As String
Dim strGrpsArr() As String
Dim lngPos As Long
Dim lngIndex As Long

   strAmount = Format$(Amount, "#") 'note: rounding occurs
   If Len(strAmount) < 4 Then
      FormatIndian = strAmount
   Else
      lngIndex = (Len(strAmount) - 2) \ 2
      ReDim strGrpsArr(lngIndex)
      
      strGrpsArr(lngIndex) = Mid$(strAmount, Len(strAmount) - 2)  'least significant trio
      
      lngPos = Len(strAmount) - 4:  lngIndex = lngIndex - 1       'init position variables
      Do
         strGrpsArr(lngIndex) = Mid$(strAmount, lngPos, 2)
         lngPos = lngPos - 2:       lngIndex = lngIndex - 1       'update variables
         If lngPos = 0 Then strGrpsArr(0) = Left$(strAmount, 1)   'leading non-pair group
      Loop Until lngPos <= 0
      
      FormatIndian = Join(strGrpsArr, ",")
      Erase strGrpsArr
   End If
End Function

Private Sub Form_Load()
Dim curTest As Currency

   curTest = 4456456456716#
   Debug.Print FormatIndian(curTest)
End Sub

Me.lblAmt.Caption = FormatIndian(rsCurrent.Fields("suitAmount").Value)

hi....
there's one problem in that...
if i give 4 digit with two decimal it is showing error...
what to do...
help me