Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

On a report I have a control bound to a column that can have up to 3 decimal places.

I want the number to format with commas separating thousands and millions, but I also want the number of decimal places to be automatic, so that if there is no decimal portion then no decimal at all is shown.

1234.567 -> 1,234.567
1234.560 ->  1,234.56
1234.500 ->   1,234.5
1234.000 ->     1,234

General format will give me the auto decimal places but no commas. Standard format gives the comma but is fixed to 2 decimal places. Doing my own =Format(Number, "#,##0.#") leaves the decimal point in and doesn't align properly, with extra space on the right of the number.

Do I have to write my own VB function to give the format I want? It seems silly that Access (apparently) can't do this out of the box.

This also seems really horrible, though it works.

=Replace(Replace(Replace(Replace(Replace( _
  Format(Number, "#,##0.000") & "x", _
  "0x", "x"), "0x", "x"), "0x", "x"), ".x", ""), "x", "")
share|improve this question

1 Answer 1

up vote 3 down vote accepted

Put this in the textbox format property: #,###.###

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.