Printable View
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
but during display nothing happens i.e. the display continues to be the US format.vbcode Code:
Can somebody show me how. :cry:
Thanks in advance.
Use format currency
FormatCurrency("123456789", , , , vbTrue)
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...
Another thing, when I read the documentation for this function I found thisat 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)....Quote:
Note All settings information comes from the Regional Settings Currency tab, except leading zero which comes from the Number tab.
What is the value of rsCurrent.Fields("suitAmount") ??
Formatcurrency works fine for me.
as of data available today the the value is in few lakhsQuote:
What is the value of rsCurrent.Fields("suitAmount") ??
one more thing the suitAmout is a currency field having no spl formatting..
Test this
or set the format in access table design viewCode:Dim curr As Currency
curr = 12345678
MsgBox FormatCurrency(curr, 0, 0, vbFalse, vbTrue)
Me.lblAmt.Caption = FormatIndian(rsCurrent.Fields("suitAmount").Value)Code: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
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
Have you tried the code in post #10?
:wave: :D ;) 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.:wave: :)
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.
hi....Quote:
Originally Posted by leinad31
there's one problem in that...
if i give 4 digit with two decimal it is showing error...
what to do...
help me