field properties in an Access table that is linked to Excel

[chubbers]

Hello, I'm currently setting up an Access db (2000) that is linked to an Excel spreadsheet. I unfortunately am having problems when I view the table. There are two columns with “#NUM” in some of the record fields instead of numbers. I’ve checked the properties for both problem columns; one is Text which is obviously wrong, and the other is Number with a field size Double. Both columns contain either a 1-3 digit number or its left blank; most numbers start with a 0 so the number one would be 01 (if this makes a difference).

Q1. I know there is a macro that changes Number to Text
(

http://support.microsoft.com/default.aspx?scid=kb;en-us;815277),

is there one for changing Text to Number? I’ve tried the macro selecting Number instead but that erased my data; I also tried moving the records with actual numbers into the first 10 rows, but that didn’t work either.

Q2.Why do I get a #NUM error in the column that has the correct data type and a reasonably good sized field? How would I change it if I could?
Thanks in advance to anyone with help/suggestions

 

[GingerR]

Linking can be difficult with Excel, as you can see. I usually IMPORT excel data, and let Access decide what the data types should be the first time I do it. If there are any #NUM like you say, I check for the exact cells in Excel which are coming up bad to see what the issue is. Usually, once I import, there are no problems. Sometimes the #NUM data is really a blank. What is the data in your exact problem cells?

After that, I write an append query that forces the imported data into the format(s) I want, and puts it into a 'final' table.

As for different functions, check out CDbl, CInt, CLng.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[chubbers]

Thanks for the reply GingerR, I just decided to change the fields to Text, which isn't that bad since I don't need them for calculations.
Btw the data was 1, 01-16 or blank thanks though