Excel number format not working in Access

[toad848 via AccessMonster.com]

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!

 

[Gina Whipp]

Toad848,

I am going to suggest you look up the Format function in Access. Access
will not store any text in a currency or number field so you are going to
have to switch to a text field if you plan on storing the Rs. If you just
want it to display with the Rs. you can then use the number or format but on
the form prefix the text box with Rs.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

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 via AccessMonster.com]

Hello Gina,

Thank you for your response. I was merely trying to get the values in a
certain field displayed with the Indian currency formatting. I eventually
found out that I could easily get the 'Rs.' prefix even at the table level,
but my desired format in its entirety only at the form level owning to the
fact that it's a conditional format.

Toad848,

I am going to suggest you look up the Format function in Access. Access
will not store any text in a currency or number field so you are going to
have to switch to a text field if you plan on storing the Rs. If you just
want it to display with the Rs. you can then use the number or format but on
the form prefix the text box with Rs.

Hello,

[quoted text clipped - 25 lines]

Thanks!

 

[Gina Whipp]

Toad848.

As long as you are storing the number in a number field then you only need
to 'display' it with the Rs. There would be no need to store the Rs. in the
table. In the query that the form is based on:

YourField: "Rs. " & [YourFieldContainingTheNumber] This will give you the
display at the form level. If it is based say on another field then:

YourField: IIf([ConditionalField]="?????", "Rs. " &
[YourFieldContainingTheNumber], [TheOtherChoice])

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hello Gina,

Thank you for your response. I was merely trying to get the values in a
certain field displayed with the Indian currency formatting. I eventually
found out that I could easily get the 'Rs.' prefix even at the table
level,
but my desired format in its entirety only at the form level owning to the
fact that it's a conditional format.

Toad848,

I am going to suggest you look up the Format function in Access. Access
will not store any text in a currency or number field so you are going to
have to switch to a text field if you plan on storing the Rs. If you just
want it to display with the Rs. you can then use the number or format but
on
the form prefix the text box with Rs.

Hello,

[quoted text clipped - 25 lines]

Thanks!

 

[toad848 via AccessMonster.com]

Thanks. I haven't started working on my forms yet, but when I do, I'll
probably find myself using your input.

Toad848.

As long as you are storing the number in a number field then you only need
to 'display' it with the Rs. There would be no need to store the Rs. in the
table. In the query that the form is based on:

YourField: "Rs. " & [YourFieldContainingTheNumber] This will give you the
display at the form level. If it is based say on another field then:

YourField: IIf([ConditionalField]="?????", "Rs. " &
[YourFieldContainingTheNumber], [TheOtherChoice])

Hello Gina,

[quoted text clipped - 19 lines]

 

[Gina Whipp]

Your welcome...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Thanks. I haven't started working on my forms yet, but when I do, I'll
probably find myself using your input.

Toad848.

As long as you are storing the number in a number field then you only need
to 'display' it with the Rs. There would be no need to store the Rs. in
the
table. In the query that the form is based on:

YourField: "Rs. " & [YourFieldContainingTheNumber] This will give you
the
display at the form level. If it is based say on another field then:

YourField: IIf([ConditionalField]="?????", "Rs. " &
[YourFieldContainingTheNumber], [TheOtherChoice])

Hello Gina,

[quoted text clipped - 19 lines]

Thanks!