Question

[crcastilla]

This may be an Excel question but I'm not sure.

I have linked a Excel spreadsheet into Access. The thing that they are numbers and when numbers like "00212" or "03309" are put into the access table the zero at the begging do not appear (so they are like this; "212" and "3309").

I tried to go to change something me maybe on the data type or something but it says i can not make any changes unless in the source data which is in Excel.

Is there something I could do?



Thank you in advance.

[June7]

I tested a spreadsheet link with columns set to text and custom format. The leading zeros were retained by the link. If your spreadsheet is showing the leading zeros then the cells must already have a text or custom format setting. I don't know why the link is dropping the leading zeros. Unless Access2010 handles that better than 2007.

Don't understand some of what you describe. You state 'when numbers like "00212" or "03309" are put into the access table' - do you mean values in the spreadsheet have leading zeros?

[pkstormy]

I usually 1st try to set the entire cell column as a text format. If that fails, I may create a 'dummy' row under the header row and put in 1 alpha character instead of numbers in those columns (since the import routine tends to always use the 1st row of data to determine the data type correctly even though this sometimes incorrectly interprets numbers as integers versus strings.) This row would get deleted after import. Not the best way but it works.
If that doesn't work, you can create an Import File Specification. I hate to do this since it limits the flexibility of new data and must be maintained with strict number of characters within the columns but it'll let you fine-tune what fields and the types of data. You would then use the File Specification Name as a parameter in your docmd.transfer.... command.

If it's a file you get regularly from some kind of export routine, I'd ask if they could surround this data with a double-quotes around the value to force it to see it as a string.