Number format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SITCFanTN - 27 Jun 2007 15:24 GMT

I'm importing data from a text file into a table. The downpayment amounts
for each record are shown as 000068800 for $ 688.00 or 000201400 for $
2,014.00. I've t ried every format option and changing the decimal place in
my table design but just can't get the text data that I'm importing to
display correctly. Any help you can give me is appreciated. thank you.

Reply to this Message

Ofer Cohen - 27 Jun 2007 15:36 GMT

Try

Format(Val([FieldName])/100,"#,##0.00")

Signature

Good Luck
BS"D

> I'm importing data from a text file into a table. The downpayment amounts
> for each record are shown as 000068800 for $ 688.00 or 000201400 for $
> 2,014.00. I've t ried every format option and changing the decimal place in
> my table design but just can't get the text data that I'm importing to
> display correctly. Any help you can give me is appreciated. thank you.

Reply to this Message

SITCFanTN - 27 Jun 2007 16:22 GMT

Where is this code placed? In the table in the imput mask...I'm confused,
excuse me for my ignorance, I'm new at access. Thanks

> Try
>
[quoted text clipped - 5 lines]
> > my table design but just can't get the text data that I'm importing to
> > display correctly. Any help you can give me is appreciated. thank you.

Reply to this Message

Ofer Cohen - 27 Jun 2007 16:32 GMT

This field type is text after you import it, you need to either
1. Make a new table, and after the import append the values to the new
table, and that field will be appended to numeric field.
when you run the append query, change the value to Val([FieldName])/100

Insert Into NewTableName (FieldName)
Select Val([FieldName])/100 As NewValue
From OldTable

Or,
2. Create a query based on the this table, and there use the format to
display the right value

Select TableName.*, Format(Val([FieldName])/100,"#,##0.00") As NewNumber
From TableName
******

I hope you are not getting more confused.
But because the data not always is imported the way we want it, we create
another table that has the right structure for all the fields and after the
import, the data is appended into that table

Signature

Good Luck
BS"D

> Where is this code placed? In the table in the imput mask...I'm confused,
> excuse me for my ignorance, I'm new at access. Thanks
[quoted text clipped - 8 lines]
> > > my table design but just can't get the text data that I'm importing to
> > > display correctly. Any help you can give me is appreciated. thank you.

Reply to this Message

Joseph Meehan - 27 Jun 2007 16:17 GMT

> I'm importing data from a text file into a table. The downpayment amounts
> for each record are shown as 000068800 for $ 688.00 or 000201400 for $
> 2,014.00. I've t ried every format option and changing the decimal place
> in
> my table design but just can't get the text data that I'm importing to
> display correctly. Any help you can give me is appreciated. thank you.

Tables should be considered buckets of data with a label on the bucket
describing the fields. They are not designed to view, edit or enter data.
Use queries, forms and reports for those functions.

Signature

Joseph Meehan

Dia 's Muire duit

Reply to this Message