importing text file

[Guest]

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

 

[Ken Snell \(MVP\)]

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.

 

[Guest]

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

 

[Ken Snell \(MVP\)]

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))

 

[MH]

Alternatively:

TheRightCurrency: CCur([FieldNameWithTextValue])/100

MH