linking to excel table - problems with data type

[Guest]

I'm trying to create a project that requires I link to data in excel. My
problem is that when I link to the spreadsheet, some of the properties get
set up as number instead of text. As a result, (at least I think this is
why) my query on this spreadsheet doesn't return all the results. When I
view the spreadsheet from within access, there are several cells with #Num!
in them because in the spreadsheet there is an alpha in the cell. I have
opened the spreadsheet directly in access and changed the format of the
columns to text and relinked the sheet into access, but these fields still
show up as number fields in access. How can I force these fields/columns to
be text?

Microsoft Access 2000 (9.0.3821 SR-1)

 

[jjsaw5 via AccessMonster.com]

Suzy

The reason you are getting the #NUM! is because of the cell formating in
Excel.

For example the feild where you are getting the #NUM!, the formatting of that
cell needs be to changed because Access does not recognize it. The start of
the cell might have a space in it. So check your column format in Excel. I've
had this problem before and it was because some of my columns started out
with a space.

I'll check back to see if you need anything.

Good luck, hope it works!

 

[Guest]

the cell in excel is set to text which is what i want, access thinks it is a
number field and sees text and gives me the error. I need to know how to
force access to know it is a text field not a number field. There may be
some spaces at the beginning of some cells, but that shouldn't matter in a
text field