SQL Expression: IsNull Function

[ThaoVy]

Hello,

Below is my SQL Expression:
(SELECT Sum ("tblProjectBusinessTypes"."Cost")
FROM tblProjectBusinessTypes
WHERE "tblProjectBusinessTypes"."ProjectNum" = "tblProjects"."ProjectNum")

However, I only want to pull those with no CompDate and no Cancelled Date.

CompDate: If IsNull ({tblProjectBusinessTypes.CompletedDate}) Then
{tblProjects.ProjectCompletedDate} Else
{tblProjectBusinessTypes.CompletedDate}

Cancelled Date: If IsNull ({tblProjectBusinessTypes.ClosedNoBidDate}) Then
{tblProjects.ClosedNoBidDate} Else
{tblProjectBusinessTypes.ClosedNoBidDate}

How can I accomplish this in an SQL Expression. Thanks so much in advance for your help.

 

[Bloke152]

you seem to have another table in your if statements. you will first need to join those tables together.
I have called them a and b to make it easier to read
tblprojects = a
tblprojectbusinesstypes = b

you will need to put this in your select statement

(case when b.CompletedDate is null then a.ProjectCompletedDate
else b.CompletedDate end) as compDate,

(case when b.ClosedNoBidDate is null then a.ClosedNoBidDate
else b.ClosedNoBidDate end) as Cancelled_date

 

[synapsevampire]

Post your software version, and why you care whether it's in a SQL Expression.

Recent versions might use a Command Object akin to:

SELECT Sum ("tblProjectBusinessTypes"."Cost")
FROM tblProjectBusinessTypes
WHERE "tblProjectBusinessTypes"."ProjectNum" = "tblProjects"."ProjectNum")
and
CompDate is null
and
CancelledDate is null

-k