importing text file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

2Blessed4Stress - 30 Mar 2007 20:50 GMT

I have a text file that contains currency values as 7 place numbers. For
example. 137.56 would be written as 0013756. How do I import the 7 digit
value so that I can see it in Access as $137.56? My import specification is
set to double which removes the leading zero's. However, if I then try to
change the format to currency, it adds the "." at the end resulting in
$13,756.00

Reply to this Message

Ken Snell (MVP) - 30 Mar 2007 21:06 GMT

Import the raw data to a temporary table. Then use an append query to modify
and copy the data to the permanent table. In the query, you'll be able to
output the number with the desired magnitude.

Signature

Ken Snell
<MS ACCESS MVP>

>I have a text file that contains currency values as 7 place numbers. For
> example. 137.56 would be written as 0013756. How do I import the 7 digit
[quoted text clipped - 3 lines]
> change the format to currency, it adds the "." at the end resulting in
> $13,756.00

Reply to this Message

2Blessed4Stress - 30 Mar 2007 21:34 GMT

If I format the field in my permanent table to currency w/2 decimals, then
append the temporary table to it, the rusult are still not what I want. I get
$13,756.00 and not $137.56

> Import the raw data to a temporary table. Then use an append query to modify
> and copy the data to the permanent table. In the query, you'll be able to
[quoted text clipped - 7 lines]
> > change the format to currency, it adds the "." at the end resulting in
> > $13,756.00

Reply to this Message

Ken Snell (MVP) - 30 Mar 2007 23:32 GMT

As I said, you need to "convert" the data in the query. Assuming that you're
putting the value "0013756" into the temporary table as a text field, then
you'd use a calculated field in the query to convert this to the proper
magnitude:

TheRightCurrency: CCur(Left(FieldNameWithTextValue,5) & "." &
Right(FieldNameWithTextValue,2))

Signature

Ken Snell
<MS ACCESS MVP>

> If I format the field in my permanent table to currency w/2 decimals, then
> append the temporary table to it, the rusult are still not what I want. I
[quoted text clipped - 17 lines]
>> > change the format to currency, it adds the "." at the end resulting in
>> > $13,756.00

Reply to this Message

MH - 31 Mar 2007 00:08 GMT

Alternatively:

TheRightCurrency: CCur([FieldNameWithTextValue])/100

MH

> As I said, you need to "convert" the data in the query. Assuming that
> you're putting the value "0013756" into the temporary table as a text
[quoted text clipped - 27 lines]
>>> > change the format to currency, it adds the "." at the end resulting in
>>> > $13,756.00

Reply to this Message