How to test for error in SQL?

[Guest]

Hi,

I have to migrate data from a spreadsheet into database.
When I link the spreadsheet into my database for processing, one of the
fields (which Access defines as Number/Double) contains spaces in some
records. This field displays #Num in those records.

Is there a way in SQL to determine whether a particular field has errored?

I have tried to use IsError function on that field - it correctly returns
False for valid records and #Num for invalid ones...

Please help..

 

[TC]

Why not import it into a text field initially. Then you can use
IsNumeric() in an SQL statement, to determine whether the field is
numeric or not. If it /is/, you coul then use Val() to store its
numeric value into a numeric field. If it /sin't/ numeric, IsNumeric()
will tell you that, so you can do something else with it.

NB. It might be IsNumber(), not IsNumeric() - I have to run off right,
now & don';t have time to check!

HTH,
TC

 

[Tim Ferguson]

Is there a way in SQL to determine whether a particular field has
errored?

Not tested, but this could work:

select etc, etc,
IIF(isnumeric(field5, field5, NULL)) AS NewField5,
etc
from


Tim F

 

[Douglas J. Steele]

Not tested, but this could work:

select etc, etc,
IIF(isnumeric(field5, field5, NULL)) AS NewField5,
etc
from

I believe that should be

select etc, etc,
IIF(isnumeric(field5), field5, NULL) AS NewField5,
etc
from

 

[Guest]

You could import the data into a table as text and use the Replace function
to remove all spaces.

 

[Tim Ferguson]

I believe that should be

select etc, etc,
IIF(isnumeric(field5), field5, NULL) AS NewField5,
etc
from

Thanks Doug, good catch.


Tim F

 

[Guest]

Sorry - does not work - still returns #Num

using IsNumeric function would be useful on a Text field - then I could have
used it :-(

Have found a solution though - importing query results into a temp table
ignores incorrect values and replaces them with Nulls - one extra step - a
relatively small price to pay.

Still surprised though that there is no way to test for invalid data in a
field...

 

[Tim Ferguson]

Still surprised though that there is no way to test for invalid data
in a field...

It's an Excel problem, not an Access one. Excel itself does not really
recognise any kind of data type: there is one bucket that can hold nothing,
numbers, text, formulas, error values, etc etc. Some of these have
absolutely no meaning to any kind of outside application: you would you
expect Access (or Word or Powerpoint, for that matter) to respond to a
value of ###?NAME### for example?

The need is to clean up the data before it gets to Access. This is the
purpose of defining an export filter or query on the _spreadsheet_ data
before it gets anywhere near a clean platform like Access. You just can't
make meringues out of jelly.

B Wishes


Tim F