KnowledgeWare
asked on
How to handle "#Error" values in a query column.
I've got a SELECT query with one column that uses the function
DATEADD("d", RetrainingPeriod, LastTimeTrained) AS NextTimeToTrain
and a WHERE clause that says
WHERE NextTimeToTrain <= Date() OR LastTimeTrained IS NULL
The goal being a list of all the people who either have not been trained at all, or who are past due for re-training.
The problem is that LastTimeTrained can be NULL if they've never taken the course, and that makes NextTimeToTrain have "#Error" values, and whenever I try and do anything to that column I'm getting type mismatch errors. Does anybody know a way to avoid this, or to "clean" out the errors (make them NULL)?
DATEADD("d", RetrainingPeriod, LastTimeTrained) AS NextTimeToTrain
and a WHERE clause that says
WHERE NextTimeToTrain <= Date() OR LastTimeTrained IS NULL
The goal being a list of all the people who either have not been trained at all, or who are past due for re-training.
The problem is that LastTimeTrained can be NULL if they've never taken the course, and that makes NextTimeToTrain have "#Error" values, and whenever I try and do anything to that column I'm getting type mismatch errors. Does anybody know a way to avoid this, or to "clean" out the errors (make them NULL)?
iif(IsNull(LastTimeTrained ), "do something here", DATEADD("d", RetrainingPeriod, LastTimeTrained)) AS NextTimeToTrain
ASKER
Thanks for the speedy reply. I tried the statement below, and get an "Undefined Function IFF" error. It "works" without the IFF (get the #Error values).
SELECT *
FROM
(
SELECT *, IFF(ISNULL(LastTrainedDate ), DATE(), DATEADD("d", Period, LastTrainedDate)) AS NextTimeToTrain
FROM qryEnrolledPeopleLastTrain ed
)
WHERE NextTimeToTrain <= DATE()
SELECT *
FROM
(
SELECT *, IFF(ISNULL(LastTrainedDate
FROM qryEnrolledPeopleLastTrain
)
WHERE NextTimeToTrain <= DATE()
ASKER
I don't think IFF is something you can put in an SQL statement (I'd never seen it before, but I thought I'd give it a try). I can't resort to VBA code here because I'm going to be referencing this query from an external app. Any way within the SQL statement itself?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
{slight correction, jeff is correct regarding IIF and you can't use an alias in the WHERE clause}
SELECT *, IIF(ISNULL(LastTrainedDate ), DATE(), DATEADD("d", Period, LastTrainedDate)) AS NextTimeToTrain
FROM qryEnrolledPeopleLastTrain ed
WHERE DATEADD("d", Period, LastTrainedDate)) <= DATE()
back to top
SELECT *, IIF(ISNULL(LastTrainedDate
FROM qryEnrolledPeopleLastTrain
WHERE DATEADD("d", Period, LastTrainedDate)) <= DATE()
back to top
Forced accept.
Computer101
EE Admin
Computer101
EE Admin