Format a field

[ponneri]

Hi.

How do I format a numeric or currency field on a form, in the following fashion :

5,55,55,555.00

Presently I get the display as

55,555,555.00

So basically, before the decimal - from right; it will be 3 digits,2 digits,2 digits,1 or 2 digit(s).

I'm frustrated with no solution on Google search anywhere ! Lot of workarounds for Excel though.

Please help !!!

 

[MarkK]

Presently I get the display as

55,555,555.00

How did you do that? Maybe we can build on what you already know, if you tell us what you already know.

 

[missinglinq]

That's the 'Standard' Numbers Format, which is to say

$#,##0.00

But the English version of Access, at least, won't let you change that to something like

#,##,##,##0.00

which you'd think would give you the desired format.

What, exactly, does this format represent?

Maybe someone else can figure this out, simply using Formatting in the Properties Pane, but my guess is that you'll need to count the number of digits entered and then parse the data out in the AfterUpdate event of the Control.

Linq ;0)>

 

[ponneri]

Thanks for the reply.

It is the way Indian currency is represented.

5,55,55,555.00 is counted (from left) as 5 Crores, 55 Lakhs, 55 Thousands and Five Hundred and Fifty Five.

My regional settings is set to India on Win XP. However, I still get display as 55,555,555.00 with commas at the wrong places.

Any solution appreciated !

 

[MarkK]

Just curious, but is this math true...

9,99,99,999.99 + 0.01 = 10,00,00,000.00

Here's a format function and test code.

Private Sub Test12938467143()
    Dim var
    
    For Each var In Array(1234567.89, 123.45, 12345.67)
        Debug.Print MyFormat(CDbl(var))
    Next
End Sub

Function MyFormat(Number As Double) As String
    Dim tmp As String
    
    tmp = Format(Number, "##\,##\,##0.00")
    Do While Left(tmp, 1) = ","
        tmp = Mid(tmp, 2)
    Loop
    MyFormat = tmp
End Function

 

[ponneri]

Thx MarK.

I'll try the code and see.