Custom Number Formats - Show Numbers in Thousands ('000s) 1

[pbbriggs]

Hi,

I have a form that is basically displaying a financial worksheet/cash flow statement. I would like the figures to be shown in thousands (i.e., the last three digits would not show, and the digit before the final comma would be rounded up or down accordingly). For example, I'd like 456,000 to be shown as "456" and 456,999 to be shown as "457".

I know I could just take each field in the underlying query and divide by 1,000, but I would really rather just have this be a display option (because I want the user to enter the full number, not the number rounded to the nearest thousand).

In Excel, I use the custom format _(* #,##0,; but this does not seem to have any effect when I enter it into the Format field in the Properties box in Access. I couldn't find anywhere in the Access help whether the custom formats differ in Excel and Access.

If anyone knows how to do this, I'd greatly appreciate any help.

Thanks!

 

[Pampers]

How about:

#,##0,

Pampers
Keeping it simple can be complicated

 

[pbbriggs]

Thanks Pampers.

For some reason, when I enter that into the Format box, the final comma is dropped as soon as I leave the field (even if I put the semicolon after it), so it reads #,##0 .

So, then the number just formats as: 459,999. I don't know if there is some trick to custom formatting in Access that makes it different than the custom formatting available in Excel...?

 

[Pampers]

Well,
you can also use an unbound textbox to display the number. Put this in the recordsource:
=cashflow/1000


Pampers
Keeping it simple can be complicated

 

[Pampers]

Me again,

1. I found that, if the thousand seperator is a point, you can use #.##0. to hide the last 3 zero's of the value;
2. If the thousand seperator is a comma, you can use #,##0, to hide the last 3 zero's in a value;
3. You can set the way access displays the thousand seperator in the 'regional and language setting' on the control panel.



Pampers
Keeping it simple can be complicated

 

[pbbriggs]

Hi again Pampers,
Thanks for your persistence! I would definitely like to use one of the format options (vs. your unbound textbox suggestion) because my fields allow entry/edit; they are not just for viewing.

The strange thing is, even though my regional settings are set correctly, whenever I enter #,##0, in the Format property box, the last comma disappears as soon as I leave that box in the properties (and the formatting stays the same, which is to say, it's standard formatting). Do you by chance know if I need to include that whole expression in some kind of delineators? Obviously, it's not quotes or brackets, but I am not sure what else to try.

I am going to try setting the format property in the OnOpen event of the form and see whether it works any better than with the property box. I'll let you know. Thanks for continuing to try to help!

 

[Pampers]

what kind of access-version do you use, and in what format is it saved (access 2000 or access 2003...)

Pampers
Keeping it simple can be complicated

 

[Pampers]

what about:
+#.##0.;\-#.##0.

Pampers
Keeping it simple can be complicated

 

[Pampers]

or:
+#.##0.;\-#.##0.;#.##0

Pampers
Keeping it simple can be complicated

 

[pbbriggs]

Don't laugh, we're on Access 97.

 

[pbbriggs]

The formats with the periods instead of commas aren't taking, either. As soon as I leave the format field, the period disappears.

I also just tried setting the format through code (in the OnOpen event of the form). I don't get an error, but nothing happens. Maybe Access 97 doesn't recognize this particular formatting (i.e., dropping the last 3 digits).

 

[Pampers]

Sure looks like that. Sorry, I can't replicate because I don't have access 97. Meanwhile, I will try to come up with something else.

Pampers
Keeping it simple can be complicated

 

[pbbriggs]

Have a star, Pampers, for solving this issue for users of more current Access versions! I am quite sure that your original formatting suggestion:

#,##0,

would work just fine for later versions of Access.

Thanks for the help!