Data types mismatch when linking to Excel

[Ant]

I am trying to link an excel spreadsheet into an Access database using
Tool>Get external data> link. This works however a lot of the data types
are incorrect. E.g. one of the columns in excel shows names ( this is the
result of a formula), but when liked to Access that field is a number data
type and the data shows ether #Num# (where there was a name) or 0 (where
there was a blank. I have tried to change the data type in Access but it
will not let me. I have also changed the formatting of the column in Excel
but that as had no affect to the DB.



Any ideas welcome.

 

[Rick B]

You typically would not store calculated values in a table. If the fields
that make up this column can be found in other columns, then DON'T import
it. You don't need a field for FisrtName, a field for LastName, and another
field to contain combined name. Just import the First and Last name.

 

[Ant]

Thanks for the advice Rick, however the calculation is not to concatenate
the name, it is an 'IF' other filed is false then Name otherwise Blank. I
need the name linked into Access so that I can update (apend rusults) to
another table.

Thanks

 

[Rick B]

If a field is blank, then this column displays a name from another column?

That would still be a calculated field.

You could perform that same test in a query in Access. You would only need
to import the "other" field and the "name" field.

 

[Ant]

Thanks again, however the cells that contain the raw data are not in a
database format e.g. rows & columns hence the need to link them into another
sheet and build the formula.

Ant

 

[Rick B]

Cool.

What I would do is copy that row, then paste it special - Values. That will
take the result and paste it there. The end result will be the data you
need to import will be in the column. (make a copy first)

 

[Larry Daugherty]

How about changing things on the Excel side before you import?

Copy your spreadsheet. In the copy, select the whole sheet or just
the relevant column(s) and Replace formulas with Data. Import that
spreadsheet's data into Access.

HTH