wrong datatype when I link a table to excel

[madrappin]

I am trying to link a table to an excel spreadsheet, but some of the fields it automatically assigns them the data type number, when they need to be text. as a result it shows a #NUM! for that entire field. and access wont let me change the data type in design view. I am at a loss. Can anyone help? Thanks.

 

[lespaul]

I didn't know that you could "link" a table to an excel sheet. Do you mean that you are importing an excel file into a new Access table? In that case, the first value of a field determines what kind of field it is. So if you have a list of values in excel:


1234
AVB
25AD

the first value is a number, so Access creates the field as a number.

There are two ways to avoid this.

1. Create a table the way you want it and then import/append the excel sheet into this table.

2. Add a row into your excel file so that you force the correct data type. For instance, in the above list, add a text entry above 1234 so Access creates this field as text not number.

HTH

Leslie

 

[madrappin]

It is an option under Get External Data. Access lets me link them rather than import, so the changes that are made in access reflect on the spreadsheet as well. This would work fine, except for the fact that a few of the fields are messed up.

 

[jksmi]

Hi, when you've finished in the query wizard before clicking finish select the option to 'View Data or edit query in Microsoft Query' - this will open your db query in MS Query. Click the SQL button and you can edit the underlying SQL, now you can format the field using the CStr function. Here's one I made earlier

SELECT cstr(BUSINESS1.BUSINESS_ID) AS 'ID'
FROM `C:\zt\db1`.BUSINESS1 BUSINESS1

HTH, Jamie
FAQ219-2884

 

[lespaul]

well, you learn something new everyday!

leslie