Thread: IF Clause in where statement
02-22-12, 10:28 #1Registered User
- Join Date
- Nov 2011
- Baltimore, MD
Unanswered: IF Clause in where statement
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])
((tblCases.[DischargeDate2]) Between [enter startdate] And [enter enddate]))
Can someone tell me what's wrong with this code?
02-22-12, 11:56 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
without seeing the table(s) design then no
seeing columns such as dischargedate2 doesn't fill me with any hope though.
because dates are easy to screw up when requesting user input AND you have no means of validating Id suggest you use a form to enter the parameters
as I understand your criteria
you want to pull all records where there is a discharge date within the specified range
(not isnull(dischargedate) ans dischargedate between startdate and enddate)
OR (not isnull(dischargedate2) and dischargedate2 between startdate and enddate)I'd rather be riding on the Tiger 800 or the Norton