How to test for error in SQL?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sergey Poberezovskiy - 22 Dec 2005 03:30 GMT

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..

Reply to this Message

TC - 22 Dec 2005 09:02 GMT

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

Reply to this Message

Tim Ferguson - 22 Dec 2005 09:40 GMT

"=?Utf-8?B?U2VyZ2V5IFBvYmVyZXpvdnNraXk=?="
<SergeyPoberezovskiy@discussions.microsoft.com> wrote in
news:78DA5954-7252-48DC-B278-3AB9625D1CA2@microsoft.com:

> 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

Reply to this Message

Douglas J. Steele - 22 Dec 2005 11:11 GMT

> "=?Utf-8?B?U2VyZ2V5IFBvYmVyZXpvdnNraXk=?="
> <SergeyPoberezovskiy@discussions.microsoft.com> wrote in
[quoted text clipped - 9 lines]
> etc
> from

I believe that should be

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Reply to this Message

Tim Ferguson - 22 Dec 2005 20:55 GMT

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in news:#
5UM3huBGHA.2912@tk2msftngp13.phx.gbl:

> I believe that should be
>
> select etc, etc,
> IIF(isnumeric(field5), field5, NULL) AS NewField5,
> etc
> from

Thanks Doug, good catch.

Tim F

Reply to this Message

Sergey Poberezovskiy - 22 Dec 2005 22:51 GMT

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...

> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in news:#
> 5UM3huBGHA.2912@tk2msftngp13.phx.gbl:
[quoted text clipped - 9 lines]
>
> Tim F

Reply to this Message

Tim Ferguson - 24 Dec 2005 12:39 GMT

"=?Utf-8?B?U2VyZ2V5IFBvYmVyZXpvdnNraXk=?="
<SergeyPoberezovskiy@discussions.microsoft.com> wrote in
news:93A26A03-3C8E-4FD5-9574-63AD59776EB4@microsoft.com:

> 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

Reply to this Message

John - 22 Dec 2005 12:39 GMT

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

> Hi,
>
[quoted text clipped - 9 lines]
>
> Please help..

Reply to this Message