HELP! format not working

manish wrote:

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.

--
Marsh
MVP [MS Access]

MARSH
thanx a zillion times for the code. since i do not know
about VBA i really could not have achieved my purpose.
thanx a lot..really.
By the way the currency is Indian Rupees. Country - India.
We use units like
ones
tens
hundreds
thousands
ten-thousand
lakhs
ten lakhs
crores
ten crores
this is just for ur information only.
thanks
-------manish-----


-----Original Message-----
manish wrote:

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.

--
Marsh
MVP [MS Access]
.

You're welcome for the code, but I am still surprised your
regional settings doesn't tell the Currency format how to do
it in your region's standard way.
--
Marsh
MVP [MS Access]



wrote:

MARSH
thanx a zillion times for the code. since i do not know
about VBA i really could not have achieved my purpose.
thanx a lot..really.
By the way the currency is Indian Rupees. Country - India.
We use units like
ones
tens
hundreds
thousands
ten-thousand
lakhs
ten lakhs
crores
ten crores
this is just for ur information only.
thanks
-------manish-----


-----Original Message-----
manish wrote:

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.

--
Marsh
MVP [MS Access]
.