How to use IsError()?

[Phil Smith]

I have a spreadsheet that I will be importing on a regular basis. This
is formatted for human consumption, with multiple header rows, etc.
Fortunately, I only need a single piece of data from the various rows,
and they can be easily selected from the raw import.

I import the spreadsheet, and the second column imports as text, due to
the column headers scattered throughout. I only need the data from that
second column, and I can differentiate it from the headers by the fact
that it is a number, and can convert to one.
The idea is simple. Convert the field to a number. If it is
successful, I want it. If it produces an error, I don't.

SELECT CDbl([Hardgoods]![Last Update]) AS SKU,
IsError(CDbl([Hardgoods]![Last Update])) AS Expr2
FROM Hardgoods

This query produces "#Error" for both pieces. IsError() is not giving
me a way to determine if it is an error, because if it is an error, it
is returning #Error. I thought it was SUPPOSED to return a -1, (true)
It DOES return a 0 if it is NOT an error. Since I want those records
which are NOT an error, I try

SELECT CDbl([Hardgoods]![Last Update]) AS SKU,
IsError(CDbl([Hardgoods]![Last Update])) AS Expr2
FROM Hardgoods
WHERE (((IsError(CDbl([Hardgoods]![Last Update])))=0));

and I get "Invalid Use of Null"

So just exactly what good is IsError() if it can't handle errors?

I'm confused.

 

[John Spencer]

I've never tried to use the isError function in a query.


SELECT IIF(IsNumeric([Hardgoods]![Last Update]),
CDbl(HardGoods![Last Update]),Null) AS SKU
FROM Hardgoods

OR
SELECT CDbl(HardGoods![Last Update])
FROM HardGoods
WHERE IsNumeric([Hardgoods]![Last Update])

You might try the following with IsError, but I think it will fail.

SELECT CDbl(HardGoods![Last Update])
FROM HardGoods
WHERE IsError(CDbl([Hardgoods]![Last Update]))=0

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County