Number as text

[rlp]

I am linking an Excel sheet to Access 2000. I have two fields that should be treated as Text and are defined as Text in Excel. Both contain some items that are alpha-numerical and some that are all numerical. The link sets up both fields as Text, but items that are all numerical are being brought in as numbers and I receive the Num#! error on those items. If I put a space in front of the number items, it reads as text which is a possibility but would require editing all new data. I thought the Text label meant read everything as Text. Suggestions on how to get it to bring in any data in these fields as text?

 

[WildHare]

It sounds as if Access is setting a data type based on the first row it sees? Can you dummy up an earlier row in the Excel s/s that has a text guy there, not just a number treated like text?

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[rlp]

Thanks for your comment. I have been able to get the column set as both number and text. Number setting won't read text and text setting won't read numbers.

Subsequent to my message, I found documentation on Microsoft website that Access determines col type by first 8 items in the column (don't know exactly what happens when it is mixed). Their doc indicated that numerical items to be treated as Text have to be converted to include a blank space at the front in order to be read as text. I don't understand why that should be necessary, but that seems to be the solution. They provide a macro to do that but I think it is easier to do in Excel with concatenate or & function.