PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] format currency to Indian format in Label/Textbox-VBForums
Results 1 to 15 of 15

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

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2006
    Location
    India, Punjab, Bhatinda
    Posts
    1,689

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

    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
    vbcode Code:
    1. 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.

  2. #2
    Fanatic Member amrita's Avatar
    Join Date
    Jan 2007
    Location
    Orissa,India
    Posts
    888

    Smile Re: format currency to Indian format in Label/Textbox

    Use format currency

    FormatCurrency("123456789", , , , vbTrue)

  3. #3
    Just Married shakti5385's Avatar
    Join Date
    Mar 2006
    Location
    Udaipur,Rajasthan(INDIA)
    Posts
    3,747

    Re: format currency to Indian format in Label/Textbox


  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2006
    Location
    India, Punjab, Bhatinda
    Posts
    1,689

    Re: format currency to Indian format in Label/Textbox

    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...

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2006
    Location
    India, Punjab, Bhatinda
    Posts
    1,689

    Re: format currency to Indian format in Label/Textbox

    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)....

  6. #6
    Fanatic Member amrita's Avatar
    Join Date
    Jan 2007
    Location
    Orissa,India
    Posts
    888

    Smile Re: format currency to Indian format in Label/Textbox

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

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2006
    Location
    India, Punjab, Bhatinda
    Posts
    1,689

    Re: format currency to Indian format in Label/Textbox

    What is the value of rsCurrent.Fields("suitAmount") ??
    as of data available today the the value is in few lakhs

  8. #8

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2006
    Location
    India, Punjab, Bhatinda
    Posts
    1,689

    Re: format currency to Indian format in Label/Textbox

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

  9. #9
    Fanatic Member amrita's Avatar
    Join Date
    Jan 2007
    Location
    Orissa,India
    Posts
    888

    Smile Re: format currency to Indian format in Label/Textbox

    Test this

    Code:
    Dim curr As Currency
    curr = 12345678
    MsgBox FormatCurrency(curr, 0, 0, vbFalse, vbTrue)
    or set the format in access table design view

  10. #10
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: format currency to Indian format in Label/Textbox

    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
    Me.lblAmt.Caption = FormatIndian(rsCurrent.Fields("suitAmount").Value)
    Last edited by leinad31; Feb 27th, 2007 at 07:10 AM.

  11. #11

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2006
    Location
    India, Punjab, Bhatinda
    Posts
    1,689

    Re: format currency to Indian format in Label/Textbox

    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

  12. #12
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: format currency to Indian format in Label/Textbox

    Have you tried the code in post #10?

  13. #13

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2006
    Location
    India, Punjab, Bhatinda
    Posts
    1,689

    Exclamation Re: format currency to Indian format in Label/Textbox

    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.

  14. #14
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: format currency to Indian format in Label/Textbox

    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.
    Last edited by leinad31; Feb 28th, 2007 at 07:38 AM.

  15. #15
    Junior Member
    Join Date
    Sep 2010
    Posts
    29

    Re: format currency to Indian format in Label/Textbox

    Quote Originally Posted by leinad31 View Post
    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
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width