Excel number format not working in Access

[toad848]

Hello,

I am using Access 2002 SP3 and Excel 2002 SP3. I have come up with the following format, which I need for a particular application:

[>=10000000][$Rs.-4009]##\,##\,##\,##0.00;[>=100000][$Rs.-4009]##\,##\,##0.00;[$Rs.-4009]##,##0.00

The above format, when used in Excel, displays a number with commas placed based on the Indian number system, two decimal places, and "Rs." prefixed to the number (to indicate the Indian currency "Rupees"). For instance,

10000000 gets displayed as Rs.1,00,00,000.00

This works consistently in Excel. When I copy and paste this format into the format box corresponding to an Access table field and hit enter, however, Access automatically converts my format into

>\["=10000000]""[$Rs.-4009]##,##,##,##0.00";>\["=100000]""[$Rs.-4009]##,##,##0.00";\["=10000000]""[$Rs.-4009]##,##,##,##0.00"



which obviously doesn't work. This happens irrespective of whether I set the data type for the field to be Currency or Number. Could someone suggest a way for me to get the formatting I want in Access?

Thanks!

[toad848]

Any ideas? I'd much appreciate help with the issue I've described above.

Thanks!

[thhui]

To workaround, write a string function instead

Function RCur(a)
    If a >= 1000 Then
       RCur = "Rs " & Format(a, "##\,##0.00")
    End If
    If a >= 100000 Then
       RCur = "Rs " & Format(a, "##\,##\,##0.00")
    End If
    If a >= 10000000 Then
        RCur = "Rs " & Format(a, "##\,##\,##\,##0.00")
    End If
End Function

[toad848]

To workaround, write a string function instead

Function RCur(a)
    If a >= 1000 Then
       RCur = "Rs " & Format(a, "##\,##0.00")
    End If
    If a >= 100000 Then
       RCur = "Rs " & Format(a, "##\,##\,##0.00")
    End If
    If a >= 10000000 Then
        RCur = "Rs " & Format(a, "##\,##\,##\,##0.00")
    End If
End Function

Thanks for your input, thhui. I've abandoned the idea of getting this formatting in my tables, and resigned myself to just getting it in my forms. I haven't started working on the latter, but when I do, I'll probably find myself using your algorithm.

Thanks again!