Data types

[rodeo0851]

I have an Excel file (sourced externally) linked as a table. All fields are defined as text fields. In some of the Excel cells, data has somehow been entered as numeric (eg phone number). When I query the Excel file thru Access, these fields show up as errors (#num!).

I need to find a way of interogating the field, and if in error (like these ones), then ignore it, or replace with zeros etc.

I cant find a way of doing this. Help

Rod

 

[neileg]

You can't 'define' a field type in Excel. They are what they are. So if you have a mix of text and numeric data in a column you are going to have problems no matter what you do with a linked table.

You have two choices, either force the data in Excel to be text by inserting a new column and entering a formula like:
=A1&""

Or you can import the data into Access which will allow you to define the field as text, and either data type will import to a text field.