Textbox format including commas

[PeteG]

Hi,

I am trying to add this customer format to a text box - #,##0.0,, . The problem is that the trailing commas get interpreted as a string that gets appended to the number rather than part of the formatting. So, when I enter that format, either manually or via code, the format gets changed to #,##0.0",,".

I want to format the text box so that, for example, 445,477,255.25 would be displayed as 445.5. The above format is fine in Excel. I've tried changing the format of both the textbox and also the table field that the textbox is bound to, with the same result.

Does anybody have any way around this or an alternative to formatting in this way?

Thanks

 

[PeteG]

Apologies, I meant to write "custom format" rather than "customer format"!

 

[dhookom]

Do I understand correctly that you want to display the value divided by 1,000,000?

Duane
Hook'D on Access
MS Access MVP

 

[PeteG]

Yes, that's it. Well, divided by one million and rounded to one decimal place.

 

[PeteG]

Just to add to that, I could change the control source to something like "[textbox]/1000000". I think this would work in the same way. One thing I would definitely lose, however, is the ability for the user to 'click' into the textbox and see the value to it's original accuracy and I'd prefer them to be able to see that if possible.

 

[dhookom]

There are lots of alternatives such as:
- use two text boxes
- add code to put the original value into the ControlTip Text property
- use a double-click to open a dialog with the original value
- use code to place the original value in the Status Bar

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Also, the text box (one divided by 1,000,000) will display 445.47725525 when the user clicks into it. All they have to do is multiply by 1,000,000.

Duane
Hook'D on Access
MS Access MVP

 

[strongm]

Unfortunately, I think excel cell formatting is about the only place where your custom format works as you want it to. It doesn't work in VBA, it doesn't work on userforms, and it doesn't work - as you have discovered - on an Access form

 

[PeteG]

Thanks for all the above. I might just disable the control so that the user does not see the underlying number. I think that may be better than them seeing it to many decimal places (I know, they can just multiply it by one million but IF they don't need to see it, it'll probably avoid any confusion if they don't!)

This was a late change to the spec that I am looking at....one problem with changing the source from a bound field to a formula is that it is possible that the code will update the text box value in some cases and of course that will fail with a formula there. So, I'd need to amend the code to update the record rather than the text box value - not a big change but I just need to take all this on board and come to a solution that fits as closely as I can make it.

Thanks again for the advice.