An interesting problem! (Don't you just hate interesting problems?!).
Some experimentation confirms that non-numeric values in fields in a
column of an Excel spreadsheet which has been linked as a numeric
field in an Access Table cause a "level" of error (if you try to
reference that field in a query) which, in essence, prevents you from
doing anything useful at all with that field. I can see the logic
behind this, but it is a pain when you want to do the sort of thing
that you are trying to do.
I can think of a number of ways of getting around this - none of them
is terribly simple, but the second would probably be my preference:
1) Instead of _linking_ to the spreadsheet table, _import_ it as a new
temporary Access Table. The Import wizard will give you the same
default data types for your spreadsheet columns, but rows containing
data that cannot be converted will be turfed into a separate
"Conversion Error" Table, which you can handle separately.
2) Again, instead of linking, import the spreadsheet data into an
existing (previously cleared) table, in which all the fields which
should be numeric but may contain non-numeric data are declared as
Text fields. You can then use a Query to apply IsNumeric() to these
questionable fields, convert the valid results to numbers and return
what you want for the invalid ones.
3) Link as you originally did, but write a Function to scan through a
Recordset based on the linked Table, record by record, appending the
data to a second temporary Table which you will use for your
subsequent processing. In order to copy the records, you will
obviously have to "look at" the value of each field - trap and handle
appropriately the errors which will be thrown when you try to look at
the "bad" values.
Hi Peter,
The Excel fields are supposed to be a series of numbers exported from a
proprietary database whose properties I cannot change. The database
generates a series of tables (addresses in one, figures in an other) and
these are the tables I download and save onto my machine. In the numerical
table most of the export fields are populated but those which aren't give
rise to the #NUM! in the table I download. Because the excel table is
overwritten every time a new download is done I cannot simply put in a
formula into Excel. In fact that would be cumbersome because I cannot know
in advance if I shall have 50 lines, 100 lines or 500 lines of output. Once
I download the table it is then treated as a link table to an Access Database
which does some more work on the tables to transform them into a form that is
acceptable to themaster database. This work involves renaming some fields,
ordering them in a different fashion and linking up some information. This
is all secondary to the first task which is to clean up the information by
replacing (for example) #NUM! by something more understandable to
non-technical users say "n/a" for example or even ""
Hope I have been clear!
Jon
Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
