Erroneous Errors!

J

jonmarcr

I've a link table with excel to access. some fields have a #NUM! - I cannot
change the excel sheet because that is generated by another process over
which I have no control. I want to filter out the #NUM! and have tried
building an expression such as IIf(IsError.... but that stubborn #NUM!
doesn't want to disappear. Any hints please?
 
J

jonmarcr

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
 
J

jonmarcr

Hi Peter, Thanks for this. Well, interesting problems are what life is all
about; after all, if everything worked we might just lose interest... I will
have to experiment. I went through option "2" but actually although I got
the IsNumeric() to work when the number is a number it was still being
stubborn on the #NUM1. I converted everything to text and still had the same
problem (breaking the link and re-getting external data). I then did a find
and replace by putting zeros in and that worked but of course the issue will
be how to automate that. I don't want the database operator to fiddle -
they'd do it wrong anyway... I think I understand what you suggest in the
final option but that will require some experimentation. The max number of
records we'd ever have to deal with would be 200 or so with 24 columns of
numbers. That should not slow down the process. I do not have enough
experience so will have to start climbing the knowledge/experience curve!
Thanks for your help.
 
Top