Copy and Paste from Excel

[OEB]

I have an excel spreadsheet that contains over 20 records from another
database. I can't import that tables in the old database because the data is
in several different places. Anyway, whenever I Paste Append, it gives me an
error message saying the value I entered isn't valid for this field. Doesn't
give me any indication what is valid, and the paste table is exactly the same
values as the table layout. I tried importing too and get errors, so that's
why I am doing it this way. What is wrong? Any suggestions?

 

[Jeff Boyce]

Sometimes Access has to make assumptions about datatypes when importing data
from Excel. To get around this, and to give you an opportunity to convert
what is likely unnormalized (Excel) data into well-normalized Access data:

* first, create a "temp" table with all fields as text type
* import the Excel data into this table
* create "parsing" queries to both convert the data into its proper data
type AND to append data into the more permanent, well-normalized Access
tables

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[OEB]

Well almost. I did that, but I am still getting the invalid error. The
frustrating part is that it doesn't tell me which field is incorrect. How
can I find that out? I have 74 fields and over 100 records. When I imported
the data into the table the data in one field was wrong and it told me
exactly which on it was. Now I connected my form to the new table and
everytime I go to the next record I get the invalid message, but of course it
won't tell me which one is wrong. Any suggestions here?

Thanks.

 

[Jeff Boyce]

I don't have enough information to go on for a diagnosis.

I will point out that "74 fields" is probably indicative that further
normalization is needed.

If this were mine, I try importing the first half of the records and see if
it works. I'd break the import into chunks until I found which record was
causing the error, then focus in on that one...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[saima]

asafafafaf

Sometimes Access has to make assumptions about datatypes when importing data
from Excel. To get around this, and to give you an opportunity to convert
what is likely unnormalized (Excel) data into well-normalized Access data:

* first, create a "temp" table with all fields as text type
* import the Excel data into this table
* create "parsing" queries to both convert the data into its proper data
type AND to append data into the more permanent, well-normalized Access
tables

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP