Linked Tables (1 Viewer)

[jer]

I have a linked excel table as the overall source for my db. However when the table is linked and i open it in access, some of the columns of information display #NUM!. All the fields are formatted to text fields so I dont know whats going on! Help Please!

Jer

 

[James Dudden]

There can be lots of reasons for seeing this e.g. if a column is formatted as text and Access sees it as text but the value is a number or a date then it will show the dreaded #NUM!

You can get round this by forcing it to see the number as text by putting a ' in front of the number.

Have a look what cell the #NUM! is in in Access and then work out what the actual value is in Excel and let me know.

 

[jer]

Thanks but I figured it out. I ended up running a macro in Excel which solved the problem so that Access could read the spreadsheet.

 

[gemma-the-husky]

i would not like to use excel sheets as tables in access directly

the trouble is, you can put any data in an excel cell, which is diametrically opposed to sound database principles.

 

[Millah]

You can always view the design of the linked table to see what datatype access thinks the field contains. Anything that is not of that datatype will give you an error.