Format large numbers in a table

O

Onne

Hi

I'm facing this problem.
In a column in one of my tables really large numbers are stored. This
doesn't increase readability. So what I like to do is set a format to this
column which turns numbers like 43235489 into 43.235.489.
This should not influence calculations. Besides that I prefer that it's
still possible to enter a value into the field without the dots. (access
should add the dots itself)

The column probably doesn't need to store comma-numbers (like 35.000,98)
probably only whole numbers. At this time the datatype of the column is
"Number" and the field size is "Long integer" but I can change those if
needed.

I think this isn't a really hard problem but couldn't find the answer in
access help, or in the community yet.
Who can help me? Thanks a lot in advance!

Greetings Onne
 
K

Klatuu

It is best not to store numbers or any other data with formatting. The
correct way to do it is to format it when you present it to humans as on
forms, in reports, or queries that may be veiwed by a user.
To store numbers with formatting, you would have to use a text field. Then
you would have to convert it to a number to do any calculation which would
only slow down your application.
 
O

Onne

Hi Klatuu,

thanks for your reply!

In fact I want users to be able to modify data in a query (that is displayed
in a form by using a subform) and not at the table directly. So if I don't
set a format at the table directly, can I set a format on the query? Or do
you advise against that as well?
And if you recommend this way of formatting the fields, how could I code
that?

Greetings Onne
 
K

Klatuu

Formatting for the query would be the way to do it. The use will be able to
read it easiy and modify the values. It will update the table, but will not
affect the formatting of the table's field.

While you have the query open in desing view, right click on the field. You
will get a dialog that says Field Properties. One of the options is format.
You may choose one of the predefined formats or create your own.
 
O

Onne

Hi Klatuu,

The first part is clear, setting the format at the query is the way to do
it, so that's the way I will do it!

Problem is to design a format myself (the euro-currency format would be
fine, but I don't want the euro sign in front of all values, so can't use
that one), that's what I can't find in the help file or anywhere else.
Although it's probably not that hard.
The only manual format I have used at some other place is "#.00" that will
round off the value to two decimals.

What I want to do now is display 1000000 as 1.000.000, but how....:S!

Greetings Onne
 
K

Klatuu

I am not familiar with European numeric fomatting. For U.S. formatting I
would use
"###,###,##0" which would format whole numbers up to 999,999,999 with no
decimals. You can allway play with it in the immediate window until you get
a format you like.
 
O

Onne

Thanks Klatuu,

that's what I was looking for, I will slightly adapt it to european
standards but thats a piece of cake now!

Greetings Onne
 
Top