Results 1 to 2 of 2
  1. #1
    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?


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts