Importing, Currency and Decimals

[rgreene]

I'm having a problem getting imported data to display what I want. I'm importing a fixed width file and one of my fields is a currency amount. The field value in the file is 12 characters with the last 2 as the cents (000000544990) I have my access field type as currency with 2 decimals (and I've tried many other settings auto,0 etc.) When I do my import it displays my data as $544,990.00 If I tell it no decimals it displays $544,990 It should be displayed as $5,449.90 somehow it keeps adding 2 characters and uses them as the cents instead of using the last 2 from my file.

Any suggestions?

Thanks for helping me out.

Rick

 

[FoFa]

Because it is looking for the decimal point. You have to divide it by 100 to obtain the correct value.

 

[rgreene]

So your saying it can't take the last 2 charaters and andd the decimal before them? Where should i do the divide by 100. inthe query or on the form? When this is all done I need to export the data back into a fixed width text format file so I need that value in the filed and not just calculated and displayed in that files.

Thnaks for the quick response

 

[FoFa]

When I have this, I usually import the data into a work table, then run an append query from that into the "real" table and do the calculation on the fly in the append query. That way it is correct in my table.
Or you can run update query and do the math, but usually only iof the table ONLY contains the new imports. If old data resides in the table also, this is not the best approach because you do not want to do the math more than once on the data.

 

[rgreene]

I'll give that a try. Thanks so much for your help on this!!!

 

[rgreene]

Well I'm not familiar withthis so I'm having a little problem. I've started creating the append query but I'm not sure how to do the calculation part. Can you tell me what I need to put where in ther query?

Thanks

 

[FoFa]

In designer, in the UPDATE TOO field it would be something like [InputCurrency] / 100, if you need more info. let me know.

 

[rgreene]

I'm in design mode of the append query but I don't have an Update to field, I have Field, Table, Sort, Append To, Criteria and Or. Whe I tried to put /100 in the Append to field of the "Amount" column I get the message "The INSERT INTO statement contains the following unknown field name: '/100'."

 

[FoFa]

Sorry, was going off the top of me head.
Check the attached image to see what it should look like

basically take the amount from your import table, divide by 100 and put it in your main table

 

[rgreene]

AWESOME THANK YOU worked great