Link to home
Start Free TrialLog in
Avatar of KnowledgeWare
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)?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

iif(IsNull(LastTimeTrained), "do something here", DATEADD("d", RetrainingPeriod, LastTimeTrained)) AS NextTimeToTrain
Avatar of KnowledgeWare
KnowledgeWare

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 qryEnrolledPeopleLastTrained
)
WHERE NextTimeToTrain <= DATE()
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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
{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 qryEnrolledPeopleLastTrained
WHERE DATEADD("d", Period, LastTrainedDate)) <= DATE()

back to top
Forced accept.

Computer101
EE Admin