Access linked tables and data types

[Elias824]

Ok so I'm linking access to an excel spreadsheet, in the excel spreadsheet is a client ID, the ID can be either something like U8553340, or just a plain number like 2344323. In excel I formatted the entire column to be text, but access keeps giving me #NUM errors for anything that is just a number with no text i.e 2344323. I can set it up to be a number in excel and then anything with a U or some other letter in it will give #NUM error. Shouldn't access be able to treat a number like text? this is baffling, im not doing any calculations on it.

 

[KLin]

After you set the column to text, are you relinking it in access? I just tried it out. When I first linked a spreadsheet, the column was a numeric datatype. After I changed the formatting of the excel column to text, I went into access and relinked the spreadsheet in the linked table manager. Now it sees it was a text field.

 

[Elias824]

well I figured it out, yes I did update it, and it did display as a text field, the problem was that it was giving me #Num anywhere there was number in the text field. Anyways I guess there is a bug with linking tables with that kind of data where access dosent get that it needs to treat the numbers at text even after you changed the field to it, It just thinks they are numbers. I fixed it by just going over and copying and re-pasting the numbers into excel and then it worked, if any of this makes any sense.