manish said:
i am in a very urgent need to format a number field as
follows :
##,##,##,##0.00
so that my numbers (eg. 345457000)are formatted as
34,54,57,000.00
this is actually our indian currency system where we use
lakhs, crores instead of millions & billions etc.
i posted the same question - but noone answered it. please
somebody help me!
I'm afraid that I am unfamiliar with that currency format.
Not knowing anything about it, I would expect the Currency
format to deal with whatever regional setting you're using.
But I guess that if you're asking the question, the built in
format doesn't do what you want, so you'll have to write
your own function to convert your currency values to a
string in the desired format. Here's a some sample code I
whacked together that should be enough to get you going.
Public Function MyCurrency(Amount As Variant) As Variant
Dim strTmp As String
Dim strResult As String
Dim intLen As Integer
Dim K As Integer
If IsNull(Amount) Then
MyCurrency = Null
Exit Function
End If
strTmp = Format(Amount, "#.00")
strResult = Right(strTmp, 6)
intLen = Len(strTmp) - 6
Do While intLen > 0
strTmp = Left(strTmp, intLen)
strResult = Right(strTmp, 2) & "," & strResult
intLen = Len(strTmp) - 2
Loop
MyCurrency = strResult
End Function
You can use this function in place of the Format function in
expressions.
For a report text box that you want to format this way, set
the text box's control source to:
=MyCurrency(amountfield)
You can do the same thing for non-editable bound text box on
a form. It gets kind of tricky if you need to allow users
to enter this kind of value and format what they typed.
