importing numbers

[Sherrie]

I am trying to simply copy and paste a bunch of rows with
currency number from the internet to excel to access, but
I am coming up with "The value you entered isn't valid for
this field." In access the field type is currency and in
excel I changed it to currency as well (it was brought in
from the internet looking like currency) I have even
tried playing around with the format to general number but
I keep getting that error,
Any suggestions?? THANK YOU!!

 

[kevin]

sherrie,
Have you tried to import the excel as a new table then
append that info from that new table to the table you want
the info in?

 

[Adrian Jansen]

Access makes assumptions about the datatype in Excel based on the first few
rows in the sheet. It doesnt matter what the table field types are, if
Access decides a column from the sheet contains a number, it will cause this
error, even if the field its going to is text.

The only easy work around I have seen is to place at least one alpha
character in the first row of the sheet, in each column, which forces Access
to treat it as text. Import the sheet into a temp table, then use a query
to append the records you want into your final data table, and fix up any
data typing etc within the query.


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Guest]

Yes, and that didn't work either. It copied okay into the
new table as a text, but then when I changed it to a
numeric function to be able to copy into my other table
Access gave me a message saying I would lose all my data.
Any other suggestions?
Thanks!

 

[Adrian Jansen]

Once you import the data as text into a temporary table, you dont "change to
a numeric function", what you do is do an append query, pulling the data
from the temp table to the working table, with the fields in the working
table defined as numeric where you need them. Then the only errors you
should get are where alpha characters are in numeric fields in a record
because of typing errors etc. These you will have to fix manually.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control