?
Solved

How to handle "#Error" values in a query column.

Posted on 2007-09-28
8
Medium Priority
?
219 Views
Last Modified: 2013-11-27
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)?
0
Comment
Question by:KnowledgeWare
7 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 19979710
iif(IsNull(LastTimeTrained), "do something here", DATEADD("d", RetrainingPeriod, LastTimeTrained)) AS NextTimeToTrain
0
 

Author Comment

by:KnowledgeWare
ID: 19979801
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()
0
 

Author Comment

by:KnowledgeWare
ID: 19979877
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?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 66

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 19980910
Iff works in Access, SELECT CASE works in SQL.  Which one are you using?

Also, your SQL looks coreect, although I don't see the purpose of the subquery.
SELECT *, IFF(ISNULL(LastTrainedDate), DATE(), DATEADD("d", Period, LastTrainedDate)) AS NextTimeToTrain
FROM qryEnrolledPeopleLastTrained
WHERE NextTimeToTrain <= DATE()
0
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 500 total points
ID: 19982721
IIF vs IFF

is this SQL in Access?

change your criteria too...you can't use an alias in a where statement


SELECT IIf([RetrainingPeriod] Is Null,Date(),DateAdd("d",[RetrainingPeriod],[LastTimeTrained])) AS NextTimeToTrain
FROM qryEnrolledPeopleLastTrained([RetrainingPeriod] Is Null,Date(),DateAdd("d",[RetrainingPeriod],[LastTimeTrained])))<=Date()));

J
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 19982834
{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
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20238277
Forced accept.

Computer101
EE Admin
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question