Currency designations

[Brian]

I have an Access 2007 front-end with a SQL Server 2005 back end. I wrote an
application that will be used in India and am having trouble with the
currency formatting. When defining rupees, the designation should be Rs.
00,00,00,000.00. Note that the first group to the left of the decimal point
is 3 characters with the remaining groups only being 2 characters.

I configured a computer using the Regional and Language options, setting the
number and currency options to what is required. However, I noticed the
following problems:

- Data in the SQL Server is stored correctly. When I look at the data
using the "India" computer, I see that the currency values are
Rs.00,00,00,000.00. When I look at the same database using the USA computer,
the currency values are $000,000,000.00. This would seem to be right because
of the different currency designations.

- In Access, my problems start. If the field is set for a data type of
"Currency", I see $ when using the USA computer, but absolutely nothing when
using the India computer. If I type in a value of 12,34,58,789.00, the
amount seen in the tables on the India computer is null. On the USA
computer, the amount appears as $123,456,789.00.

BTW - The data type in SQL Server is "money".

For now, I have set all of my currency fields in my forms and reports to
00\,00\,00\,000.00 and set my data type in SQL Server to decimal(18,2). This
works except that I get preceding zeros.

So to fix my problem, can anyone offer suggestions as to why the
money/currency data types don't work? Or in lieu of that, can someone
suggest a new format that would eliminate the preceding zeros?

Thanks to anyone who takes the time to read this and also offers a suggestion.

 

[Brian]

John,

Thanks for responding.

I tried the whole Regional Settings thing on a computer set up specifically
for India use. When I look at the tables through SQL Server Management on
the India computer, I see 12,34,56,789.00. When I look at the same table
through Access on the India computer, I see nothing...the data field is null.
When I look at the tables on a USA computer, I see $123,456,789.00 in both
SQL and Access.

Don't know why. I'm using Access 2007 with Windows XP Pro.

Brian

 

[John W. Vinson]

John,

Thanks for responding.

I tried the whole Regional Settings thing on a computer set up specifically
for India use. When I look at the tables through SQL Server Management on
the India computer, I see 12,34,56,789.00. When I look at the same table
through Access on the India computer, I see nothing...the data field is null.
When I look at the tables on a USA computer, I see $123,456,789.00 in both
SQL and Access.

Sorry, I really have no idea where to go from here. It appears that SQL has
the India region better covered than Windows does, and don't know what to
suggest.