Formatting fields with decimal places

[wvrider]

I have a table that has field name of Total_Tax with a data type of currency.
The data in the field can range from being blank to 999999999.99. Maximum is
9 numbers before the decimal place and 2 afterward.

My problem is that the data was imported from a text file into access as
400. However that should be formatted to 4.00 or 1234 should be 12.34.
Also a number that is 123456789 should be 1234567.89.

I further want to format it to currency for easier reading and manipulation.
So I would like to insert $ signs and delimit them with a comma. So I need
to go from 12345678 to $123,456.78, as 1234 should be $12.34

I was thinking maybe this: Format([TextField], "0.00"), or something like it
but not sure if it will work.

Any suggestions on how I can do this? I greatly appreciate it.

 

[Tom van Stiphout]

On Wed, 30 Jan 2008 18:31:00 -0800, wvrider

First off, you can write an update statement to divide the exising
values by 100.

Formatting is NOT to be in the tables, but in the forms or reports
this data is displayed in. For example you can set the Format property
of a Textbox.

-Tom.

 

[Jeanette Cunningham]

You wouldn't usually store the Total tax in a table, it would normally be
calculated when you need to use it.
There are some exceptions to this rule, this may be one of them.
Add a new field to your table, make it currency format.
Create a query with the Primary Key and the Total_tax field, add a
calculated field such as you suggested: Format([TextField], "0.00")
Switch your query to datasheet view and check that the numbers now have the
last 2 digits on the right appearing after the decimal point.
If all appears correct, turn your query into an append query and append the
values for the calculated field into that new field of currency type you
created.
You could make a copy of the table with the original Total tax field to keep
for reference, and delete that Total tax field from your new table.

Jeanette Cunningham

 

[wvrider]

Thank you all. I have been caught up at work this week and have not had the
chance to try anything. However I think it will work.

Jeanette, my database just had a field that was the total tax paid by
someone. It has their car and the total tax paid on it, that is why it was
stored instead of calculated. I do understand the reason that it should not
be stored.

I appreciate the help.

You wouldn't usually store the Total tax in a table, it would normally be
calculated when you need to use it.
There are some exceptions to this rule, this may be one of them.
Add a new field to your table, make it currency format.
Create a query with the Primary Key and the Total_tax field, add a
calculated field such as you suggested: Format([TextField], "0.00")
Switch your query to datasheet view and check that the numbers now have the
last 2 digits on the right appearing after the decimal point.
If all appears correct, turn your query into an append query and append the
values for the calculated field into that new field of currency type you
created.
You could make a copy of the table with the original Total tax field to keep
for reference, and delete that Total tax field from your new table.

Jeanette Cunningham

I have a table that has field name of Total_Tax with a data type of
currency.
The data in the field can range from being blank to 999999999.99. Maximum
is
9 numbers before the decimal place and 2 afterward.

My problem is that the data was imported from a text file into access as
400. However that should be formatted to 4.00 or 1234 should be 12.34.
Also a number that is 123456789 should be 1234567.89.

I further want to format it to currency for easier reading and
manipulation.
So I would like to insert $ signs and delimit them with a comma. So I
need
to go from 12345678 to $123,456.78, as 1234 should be $12.34

I was thinking maybe this: Format([TextField], "0.00"), or something like
it
but not sure if it will work.

Any suggestions on how I can do this? I greatly appreciate it.