If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to use IsError()?
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. |
#2
|
|||
|
|||
How to use IsError()?
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 Phil Smith wrote: 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. |
Thread Tools | |
Display Modes | |
|
|