Mike,

Likewise, I really don't want to tell the client (international Fortune 500
company) how to do their job.  You make an excellent point by suggesting I
follow up with the client to determine what the nulls actually mean.  I have
done this and have gotten a response of "I wasn't aware you were receiving
nulls", suggesting that a bug in automation has been overlooked.  There is
some likelihood that the client will make an adjustment in the file, however,
I cannot rely on this.  So, I have to move on to working with the data in the
state it is sent.  Regardless of any solution, I still need to interpret
accurately what the "null" means.  That will be forthcoming.  Now, on to
handling the data in the form I receive it in.  Recall the simple table...

        A           B
1       34.32%      =value(A1)
2       87.29%      =value(A2)

Working fine, except where null values exist (or a possible "space" in the
field).  Peter suggests using the following formula to accommodate the
nulls...

=IF(ISERROR(VALUE(A1)), 0,VALUE(A1))

I'm assuming this replaces the B1 formula.  I haven't applied it yet, but I
can see the logic of the statement.  It's always nice to learn new things!
Can't wait for the next bottleneck.

Thanks guys for helping me out here.

regards,

Paul Roeth


<<  Peter's formula will work as long as it is OK to substitute a zero
 percent where there was no number before.  If your client data contains
 some values that are zero percent and some "blank" cells AND your
 access database needs to know a real zero percent vs. a substituted one
 then you will either have to get your client to give you the data in a
 different form or write a macro to validate the data.  I suspect some
 of the "blank" cells are really cells with a space in them.  You can
 determine this by clicking on the blank cell and hitting F2.  Watch the
 cursor indicator while you hit the left arrow key and see if the cursor
 moves.  I don't like to ask my clients to change the way they work (it
 usually doesn't work anyway) but would rather code for "errors" they
 might make (i.e. hitting the space bar to clear a cell instead of
 hitting the delete key).  At least a macro would speed up the process
 especially if you have to do it frequently or for a large amount of
 data.

 Mike >>

             PCSOFT maintains many useful files for download
                     visit our download web page at:
                  http://freepctech.com/downloads.shtml