Data type problem when I link an Excel sheet

[alx]

Hello

I get a data type mismatch in a JOIN query between 2 linked tables (Excel sheets). In the table A the column 1 is interpreted as TEXT and in the table B the column 2 is interpreted as NUMBER. I've tried to change the format in both sheets, linked again the sheets but nothing change.

How could I force the Data Type in linked tables ?
OR
How does Access choose the Data Type ?

Thanks a lot,
Alx.

 

[Joe4]

In changing the format in Excel, simply selecting the column and changing the format may NOT change your entries. For example, if you have a column of text entries and change the column format to Number, the values will still be considered Text, because that is how they are entered.

What you want to do is after you change the column format, do a Text to Columns on the column and change the entries that way (to General for numbers, or to Text).

 

[mdmilner]

Access makes a guess as to the field format for the entire column based on the first few records in the given field. One possible option, if you wish to continue to Link is to put dummy field values in the first few fields and encourage Access to "guess" the way you want it to guess.

Another option is much more involved. Transferring the spreadsheet cell by cell into a recordset and writing it into an Access Table. Yes, you'd lose the realtime nature of changes to the XLS.

You might also setup a temporary table (format the fields) that you can write the linked table contents to and then join together with your queries.
I am not certain if all field formats will translate readily - your 'Text' field that is really 100% numbers is copied to a Number field should work. Most field types should write into string/text format fields.

 

[alx]

Thank you jmiskey ! It works !

I didn't know this feature. I will save time now because usually I worked with functions for parsing data from one column to others.

Alx.