Import .csv file problem (1 Viewer)

Hi,
I'm using the docmd.transfertext action to import a .csv file. The file is a softcopy of an invoice. I have the spec set up and I thought everything worked fine.

Now I have found that when I import the file using the code it is somehow changing my values in the (monetary) amount field.

Basically when I sum the Amount in excel, or when I copy the data and Paste Append into the table in Access, I get the right sum value. When I import the file and sum the Amount it is less than what it should be.

It is being imported into a pre-existing table and the Amount field is set to currency, and in the source file it is only to 2 decimal places anyway so I didn't think it could be about rounding.

Can anyone think why this might happen???

(ps sorry for the long-winded explanation!)

In your import spec, you haven't set up any of the monetary value fields as Integer have you?

Otherwise, can you spot specific examples of values that have imported incorrectly and describe the differences?

Mike

Are you sure all the rows are being appended? If you have warnings turned off you won't see the error message that some rows couldn't be appended.

Hi everyone,
Thankyou all for your input. This problem had me really stumped, however I have managed to solve it.

Everything seemed to check out, the same number of rows was appending, there was no rounding, there were no null values that were being omitted, and when I did a straight copy and paste, the amounts summed correctly!

But what was hapenning was that the original source file was in excel format. When I did the copy and paste it was from this format. However to maintain certain data type requirements we saved this file as a .csv so as to do a transfer text. What I eventually discovered was that one text field had a comma in the middle of it and when it was imported, it moved half the record over by one column and that changed my amount value for that record. That was the exact amount I was missing.

Glaringly obvious....yes
staring me in the face.....yes

Guilty as charged, but as you all know sometimes it's the most simple thing that we don't see right away!
I hope my explanation can help someone else who encounters this.

Thanks again!