How to customize comma notation in a number ?

[bharatkk]

Hi everyone,



I am from India and our comma notation to denote number is different than western countries and we represent our number in lakhs, crores etc. instead of billions and millions. For example, in western countries one billion will be represented as 1,000,000,000/- but in India it would be represented as 1,00,00,00,000/-.

The Regional settings in my Laptop is already set to my country and the above works fine in In Excel, however, this doesn't work in MSExcess 2007. I have tried many combinations and I have been after this for last many days. On searching the internet there are many links for Excel but none for Access and now I am not sure if there is a solution for Access at all.

If anyone could help me it would be great, otherwise I think the Access is not suitable for my requirement. Then can anyone suggest an alternative for Access 2007 where I would get solution to my problem?

Thanks in advance,

Regards,

Bharat

[ranman256]

Did you try using the FORMAT property of the field in a query?
right click the field on the query line,format, enter you India format string.

[bharatkk]

Thanks you very much for your response.
Yes I tried "#,##0" which works fine in excel but not in access.
Thanks anyway.

[aytee111]

The Format function will format anything into anything, all you need to do is get it right. Google how to format numbers in Access.

The main difference between Excel and Access is that the latter is a data storage and retrieval tool. That should determine which tool to use, not how much knowledge you have.

[ranman256]

The format works perfectly. I use it a lot. But since yours is an odd structure, it may require vb.

[CJ_London]

the 'easy' format is

## ## ## ###

or

&& && && &&&

which displays separated with spaces, not commas

12 34 56 789

in the context of formatting, the comma is a special character which will only work as a '000 separator. If your numbers are always that large, you can display the comma as a literal character e.g.

&&\,&&\,&&\,&&&

which will display

12,34,56,789

but for smaller numbers you will get preceding commas - e.g.

,,56,789

Looking around the internet, there does not seem to be an easy solution with other systems either. Found this link which may be of help and explains a bit more, but does mean more work to apply

http://allenbrowne.com/ser-44.html

[bharatkk]

Thanks everybody for responding to my query. I am a novice to MS Access so obviously I do not have much knowledge. So with whatever little knowledge I have I tried my best, including most of the suggestion above, but unfortunately I am unable to find solutions at my end.

Thanks you very much everybody once again.

[bharatkk]

I found the solution in MS Office 2010 and the format "#,##0" worked for me.

Thank you all for your support and feedback.