Format a numeric or currency column ?

[ponneri]

Hi All.

How do I format a numeric or currency field in my table, so that it stores and displays the number in Indian (INDIA) number system ?

12575000 should be displayed as 1,25,75,000.00 (without decimal also fine) and not as 12,575,000 !

I've tried the regional settings option but it does not help. Just the currency symbol Rs. gets added; not the 3,2,2,1 (from the right) formatting I need.

Any help will be greatly appreciated. I need a solution badly !!

Googled a lot, some alternative in Excel suggested but none in Ms- Access. Frustrated :-(

 

[jdraw]

Please tell us more about this

12575000 should be displayed as 1,25,75,000.00

 

[ponneri]

Sure.

It is a numeric field (Double) in my table that stores amounts in Indian Rupees. When I enter the number in a data entry form, or retrieve it in a query - it should display as 1,25,75,000.00 and not as it displays currently in Western format of 12,575,000.

To be precise, the amount in Indian currency is counted as Rupees 1 Crore, 25 Lakhs, 75 Thousands.

 

[plog]

I suggest writing your own formatting function. You pass it the numeric value, it parses out and places commas where needed. To accomplish this you would need to use these string functions (http://www.techonthenet.com/access/functions/) Len & Mid.

You would determine how many characters long each number is and then use the Mid function to carve out the data you need and put commas in the right place. Here's what the pseudo code would look like:

Function Format_IndianCurrency(in_Amount) As String

ret="Error"
' return value, will hold the string that is returned from function

int_Length=Len(in_Amount)
' gets length of input string

If (int_Length=3) Then ret=in_Amount
If (int_Length=4 Then ret=Mid(in_Amount, 1, 1) & ", " & Mid(in_Amount, 2, 3)
....

Format_IndianCurrency=ret

End Function

Then when you need the formatted value, you pass that function a value and it returns the correct string. In a query it would look like this:

FormattedCurrency: Format_IndianCurrency([YourCurrencyFieldHere])

 

[CJ_London]

this works

replace(trim(format(12345175987,"## ## ## ###.00"))," ",",") returns 1234,51,75,987.00

replace(trim(format(175987,"## ## ## ###.00"))," ",",") returns 1,75,987.00

etc

 

[ponneri]

Thanks CJ.

But when I substitute a fieldname (me.amount) in place of 12345175987 in your example; it does not work properly.

I get the 31,250,000 instead of 3,12,50,000. Why ?

 

[CJ_London]

interesting,

I only tried it in the immediate window, but when put into a query or a controlsource, the multiple # are replace with single # - i.e. # # # #.00

if you are always going to have a value in the leftmost number - rupees? then "'0 00 00 000.00" works - if value is less than 1 rupee the you get preceding zero's