Access/Excel - Linking

[MonkeyLiar]

I've an access database founded on an Excel sheet. For the most part, the fields are linked fine, however, several of the fields act weirdly. One example in particular, is a field from the spreadsheet containing an alphanumeric string as text, which under Access, in some of the alphanumeric combinations, results in the data coming out in Access as '#num!'. Under both access and Excel the field in question is set as a text field.

This problem only occurs when the record in the field DOESN'T start with a letter - eg: W116067 is fine but 1728926 is not. this must be causing the problem, but why?

 

[xlbo]

In your excel sheet, test to see if excel thinks that one of your cells which starts without an ALPHA is actually a number.

The field may be formatted as text but that doesn't necessarily make excel treat it so - especially if the formatting has been applied AFTER the data has been entered

To test, say you have one of these records in A2 (1728926)
use =ISNUMBER(A2)
If the result is "TRUE" then excel is treating this field as a number

To turn it to text, use =TEXT(A2,0) and then copy values only back into the original field

Once this is done for all these records without a starting alpha you should be ok

HTH Rgds
~Geoff~

 

[danvlas]

The trouble is in Access.
When linking the sheet, Access checks a few rows of data to 'determine' the data type of each field. If it finds a number, then it assumes it's a numeric field. Which is wrong in many cases...you may have text below. So, when parsing all rows, Access encounters a text value in a field supposed to be numeric...therefore the error...
Workaround: before linking the spreadsheet, sort it in such a way that it contains text in the first row. You may even include a dummy row at the top of the spreadsheet and ignore its data in all calculations...
Good luck,


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[xlbo]

Daniel - I have had that problem before but I don't think it is the case in this instance (although I'm quite happy to be proved wrong):

"Under both access and Excel the field in question is set as a text field."

It is my understanding that you cannot set the field format of a linked table in Access as it is done through the link wizard - therefore, if the problem was the one you were highlighting, the previous statement from MonkeyLiar could not be true

However, within excel, a field (or record) can be formatted as text but still actually be a number, hence the reason for my suggestion

Rgds
Geoff Rgds
~Geoff~