Cannot fix a data type mismatch

[Steven]

I tried to join two sheets that I impotred from Excel and received a "data
type mismatch"" error. I should have been matching two numeric type data
fields but somehow one of them was imported as text.

1. First, I opened the offending spreadsheet and changed the data type for
that field, following Access help directions. I got an error message saying
that data was eliminated. Sure enough, the entire column had been deleted.

2. Second, I opened the source doc in Excel and made the data type numeric.
Reimported. No difference.

3. Third, I tried changing the data type again. No change.

4. I cursed up a blue streak.

Any suggestions?

 

[Golfinray]

Try going to the imported table, copy and paste as structure and data to make
it a local table. Then go to design view of the table and change the
datatypes.

 

[Jeff Boyce]

Steven

One approach might be to import the data as Access seems to want to, then
use a query to convert the "text" to "number".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Steven

Another notion...

If you create a query in which you add a new field that converts "text" to
"number", you could then use that query as one of the two "tables" you join
in a separate query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Steven]

Okay, someone at work smarter than me figured it out.

I was using data from a csv file. One data field was specifed as type Long
Integer in the csv file.

When I imported this into Excel, the data type was changed to text, and the
last digit was set to zero.

Subsequent attempts to edit the field data type resulted in the field being
deleted. Sneaky attempts to block copy data into a preformatted field also
failed.

The csv file was edited to change the Long Integer to Text data type, so the
problems should go away.