Using IF Statement in WHERE Clause

[shuashef]

I know this has been discussed previously, but I'm still unclear on this...
I have a form that collects client data. A client could be removed from the system and then reinstated so they could have 1 or 2 discharge dates. I'm trying to capture all the discharged clients within a specific date range regardless if they are a 1st time or 2nd time discharge as long as they are in the inputed range.

SELECT tblCases.[Last Name], tblCases.[First Name], codeDischarge.[Discharge description], tblCases.[Discharge Date], tblCases.[DischargeDate2]
FROM tblCases INNER JOIN codeDischarge ON tblCases.[Discharge Code] = codeDischarge.[Discharge Code]
WHERE (((tblCases.WaitingList)=No)) AND tblCases.[DischargeDate2] =
CASE WHEN (tblCases.[DischargeDate2]) IS NULL THEN
(tblCases.[Discharge Date]) Between [enter startdate] And [enter enddate])
ELSE
((tblCases.[DischargeDate2]) Between [enter startdate] And [enter enddate]))

Can someone tell me what's wrong with this code?
Thanks!

 

[jdraw]

I'm quite sure that the CASE statement is not supported in MSAccess SQL.

http://www.techonthenet.com/access/functions/advanced/case.php

I would recommend not using spaces in your field and object names.

You could try something like the following (UNTESTED)

Parameters SDate datetime, EDate Datetime;
SELECT tblCases.[Last Name]
, tblCases.[First Name]
, codeDischarge.[Discharge description]
, tblCases.[Discharge Date]
, tblCases.[DischargeDate2]
FROM tblCases INNER JOIN codeDischarge ON 
tblCases.[Discharge Code] = codeDischarge.[Discharge Code]
WHERE
((tblCases.WaitingList)=No) AND 
   (
     ((tblCases.[Discharge Date]) Between  SDate  And EDate )		
     OR
     ((tblCases.[DischargeDate2]) Between  SDate  And  EDate )
   )

 

[shuashef]

This is great. Thanks!

But the problem is that I can't use a basic or statment since I would only use the 2nd discharge date when there is a date in the 1st discharge date (it won't be blank). In this case, I would want to selec the 2nd dischage date.

Any suggestions?

 

[MSAccessRookie]

I'm quite sure that the CASE statement is not supported in MSAccess SQL.

http://www.techonthenet.com/access/functions/advanced/case.php

I would recommend not using spaces in your field and object names.

You could try something like the following (UNTESTED)

Parameters SDate datetime, EDate Datetime;
SELECT tblCases.[Last Name]
, tblCases.[First Name]
, codeDischarge.[Discharge description]
, tblCases.[Discharge Date]
, tblCases.[DischargeDate2]
FROM tblCases INNER JOIN codeDischarge ON 
tblCases.[Discharge Code] = codeDischarge.[Discharge Code]
WHERE
((tblCases.WaitingList)=No) AND 
   (
[COLOR=red][B]   ((tblCases.[Discharge Date]) Between  SDate  And EDate )        [/B][/COLOR]
[COLOR=red][B]   OR[/B][/COLOR]
[COLOR=red][B]   ((tblCases.[DischargeDate2]) Between  SDate  And  EDate )[/B][/COLOR]
   )

Try replacing the code that is in RED with something like the following UNTESTED CODE:

IIF((tblCases.[Discharge Date] IS Not Null), tblCases.[Discharge Date], IIf((tblCases.[DischargeDate2] IS Not Null), tblCases.[DischargeDate2], "***")) Between SDate And EDate

Note that you did not specify the value to use if BOTH of the dates were NULL, and you will need to define that. If DischargeDate2 will not be Null, then the Query would be simpler.

IIF((tblCases.[Discharge Date] IS Not Null), tblCases.[Discharge Date], tblCases.[DischargeDate2]) Between SDate And EDate

 

[jdraw]

Try this, again UNTESTED

Parameters SDate datetime, EDate Datetime;
SELECT tblCases.[Last Name]
, tblCases.[First Name]
, codeDischarge.[Discharge description]
, tblCases.[Discharge Date]
, tblCases.[DischargeDate2]
FROM tblCases INNER JOIN codeDischarge ON
tblCases.[Discharge Code] = codeDischarge.[Discharge Code]
WHERE
((tblCases.WaitingList)=No) AND
(
(((tblCases.[Discharge Date]) Between SDate And EDate )
AND (IsNull(tblCases.[DischargeDate2]))
OR
(((tblCases.[DischargeDate2]) Between SDate And EDate )
AND (tblCases.[Discharge Date] >" "))
)