Formatting fields with decimal places

  • Follow


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.
0
Reply Utf 1/31/2008 2:31:00 AM

On Wed, 30 Jan 2008 18:31:00 -0800, wvrider
<wvrider@discussions.microsoft.com> wrote:

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.


>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.
0
Reply Tom 1/31/2008 3:28:01 AM


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" <wvrider@discussions.microsoft.com> wrote in message 
news:F672AE79-8983-40BC-B80A-C6C76AFAB863@microsoft.com...
>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. 


0
Reply Jeanette 1/31/2008 3:35:01 AM

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.

"Jeanette Cunningham" wrote:

> 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" <wvrider@discussions.microsoft.com> wrote in message 
> news:F672AE79-8983-40BC-B80A-C6C76AFAB863@microsoft.com...
> >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. 
> 
> 
> 
0
Reply Utf 2/2/2008 4:17:01 AM

3 Replies
640 Views

(page loaded in 0.057 seconds)


Reply: