CASE in WHERE Clause SQL VBA Excel

Question

I am trying to add a filter criteria for the current fiscal year in a WHERE statement in my code. However, I don't know how to integrate the CASE statement in my WHERE clause in VBA. Here's the code that I am working on:

 Source = "SELECT tblretirements.retirementID As `RetirementID`,tblretirements.InputBy As `Input By`, tblretirements.ReceiptDate As `Date Received` , tblretirements.FirstName As `First Name`, tblretirements.LastName As `Last Name`, tblretirements.DateOfBirth As `DOB`, tblretirements.DateOfRetirement As `Retirement Date`, tblBenefitInProcress.RetirementDescription As `Retirement Type`, tblretirements.MemberPIN As `Pin`, tblcurrentplan.CurrentPlan As `Current Plan`, tblretirements.ServiceCredits As `Service Crdits w/o PS/AC`, tblretirements.MultiplePlans As `Multiple Plans/Tiers?`, " & _
    " tblretirements.ConfirmationLetterMailed As `Mailed Confirm Letter`, tblretirements.WorkbookSetup As `Set Up Excel Workbook`, tblCalculationTeam.CalcTeam As `Calculation Team`, tblCalculationPersonnel.Name As `Assigned Staff`, tblretirements.ReviewDate As`Review Date`, tblBoardStatus.StatusReported As`BoardStatusID`, tblretirements.ReciprocityID As `Reciprocity?`, tblretirements.EmployerCodeID As `Employer`, " & _
    " tblDepartment.DepartmentName As `Department`, tblretirements.PendingPurchase As `Pending Purchase(s)?`, tblJoinder.Joinder As `Joinder or Pending DRO?`, tblDisabilityPending.AgendaDisability As `DisabilityID`, tblReciprocity.ReciprocalStatus, tblOrientationStatus.OrientationStatus As `Orientation Elected?`,tblretirements.OrientationDate As `Orientation Date`, tblReviewPersonnel.ReviewerName As `Designated Reviewer`, tblretirements.FileSetupApproved As `File Set-Up Approved`, tblretirements.AgendaApplication As `Agenda Application`, tblretirements.EstimateToReviewer As `Estimate to Reviewer`, tblretirements.EstimateToSupervisor As `Estimate to Supervisor`, " & _
    " tblretirements.EstimateApproved As `Estimate Approved`, tblretirements.FinalPaycheck As `Final Paycheck Date`, tblretirements.FinalService As `Final Service with PS/AC`, tblretirements.FinalToReviewer As `Final Calc to Reviewer`, tblretirements.FinalToSupervisor As `Final Calc to Supervisor`, tblretirements.FinalApproved As `Final Calc Approved`, tblretirements.ApplicationCancelled As `App Cancelled by Member?`, tblretirements.RetElectionDistributed As `Retirement Election Distributed`, tblretirements.RetElectionReturned As `Retirement Election Returned`, tblPaymentOption.AgendaOption As `Option/Payment Selected`, " & _
    " tblretirements.TempAnnuityID As `Age Request for Temp Annuity`, tblretirements.FinalAllowance As `Final Allowance Calculation`, tblretirements.Continuance As `Continuance`, tblretirements.PayrollFormsStaff As `Payroll Forms Completed (Staff)`, tblretirements.AgendaPayment As `Option-Payment`, tblretirements.PayrollFormsSupervisor As `Payroll Forms Reviewed (Supv)`, " & _
    " tblretirements.CboApprovedAllowance As `Allowance Approved (CBO)`, tblretirements.AllowanceEstimated As `Allowance Estimated?`, tblretirements.AllowEnteredInPayroll As `Allowance Entered In Payroll`, tblretirements.DistributionCycleID As `Distribution Cycle for 1st Payment`, " & _
    " tblretirements.FirstPayDate As `Distribution Date for 1st Payment`, tblretirements.AllowanceFinalized As `Allowance Finalized`, tblretirements.FileImaged As `Retirement File Imaged`" & _
    " FROM ((((((((((((tblRetirements " & _
    " LEFT JOIN tblCalculationPersonnel On tblRetirements.CoordinatorID=tblCalculationPersonnel.CoordinatorID) " & _
    " LEFT JOIN tblCurrentPlan On tblRetirements.CurrentPlanID=tblCurrentPlan.CurrentPlanID) " & _
    " LEFT JOIN tblBenefitInProcress On tblRetirements.BenefitInProcess=tblBenefitInProcress.RetirementTypeID) " & _
    " LEFT JOIN tblPaymentOption ON tblretirements.OptionID=tblPaymentoption.OptionID) " & _
    " LEFT JOIN tblReviewPersonnel ON tblretirements.ReviewerID=tblReviewPersonnel.ReviewerID) " & _
    " LEFT JOIN tblOrientationStatus ON tblretirements.OrientationID=tblOrientationStatus.OrientationID) " & _
    " LEFT JOIN tblDisabilityPending ON tblretirements.DisabilityID=tblDisabilityPending.DisabilityID) " & _
    " LEFT JOIN tblJoinder ON tblretirements.JoinderID=tblJoinder.JoinderID) " & _
    " LEFT JOIN tblDepartment ON tblretirements.DepartmentID=tblDepartment.DepartmentID) " & _
    " LEFT JOIN tblEmployerCode ON tblretirements.EmployerCodeID=tblEmployerCode.EmployerCodeID) " & _
    " LEFT JOIN tblReciprocity ON tblretirements.ReciprocityID=tblReciprocity.ReciprocityID) " & _
    " LEFT JOIN tblCalculationTeam ON tblretirements.CalculationTeamID=tblCalculationTeam.CalculationTeamID) " & _
    " LEFT JOIN tblBoardStatus ON tblretirements.BoardStatusID=tblBoardStatus.BoardStatusID " & _

This is what I need assistance with:

 " WHERE tblretirements.ApplicationCancelled = 'No' AND (tblretirements.ReceiptDate IS NULL OR tblretirements.ReceiptDate " & _
        " BETWEEN (CASE WHEN MONTH(getdate()) < 7 THEN DATEFROMPARTS(YEAR(getdate())-1,7,1) " & _
        " ELSE DATEFROMPARTS(YEAR(getdate()),7,1) End ) AND  (CASE WHEN MONTH(getdate()) < 7 THEN DATEFROMPARTS(YEAR(getdate()),6,30) " & _
        " ELSE DATEFROMPARTS(YEAR(getdate())+1,6,30)End ))  "

Can someone please help me? Thank you.

Answer 1

Several issues need attention here:

1. For complex queries of many joins, do not build SQL on the fly during application code. Save it as stored object in MS Access so the engine can save best execution plan. Then have application, here being Excel, reference the query by name. You can even pass values as parameters to a saved query.

2. Use table aliases to avoid writing out the long table names for a more readable and maintainable, and less lengthy query.

3. No two SQL dialects are the same. Even under the same vendor, here being Microsoft. The JET/ACE SQL (Windows .dll files) dialect that MS Access uses is different than the T-SQL dialect that SQL Server uses. Interestingly, another database, Sybase, also uses T-SQL with a history to why.

   But like all other dialects (e.g., Oracle, Postgres) though, both share most traditional ANSI SQL functions which is the standard of the language. Therefore, SQL Server's CASE, DATEFROMPARTS(), and GETDATE() must be replaced for Access' IIF(), DATESERIAL(), and DATE(). Interestingly though, CASE is an ANSI method.

Consider the following re-write of your query and specifically the WHERE clause to be saved in MS Access as a stored object.

SQL

SELECT r.retirementid             AS `RetirementID`, 
       r.inputby                  AS `Input By`, 
       r.receiptdate              AS `Date Received`, 
       r.firstname                AS `First Name`, 
       r.lastname                 AS `Last Name`, 
       r.dateofbirth              AS `DOB`, 
       r.dateofretirement         AS `Retirement Date`, 
       bp.retirementdescription   AS `Retirement Type`, 
       r.memberpin                AS `Pin`, 
       cr.currentplan             AS `Current Plan`, 
       r.servicecredits           AS `Service Crdits w/o PS/AC`, 
       r.multipleplans            AS `Multiple Plans/Tiers?`, 
       r.confirmationlettermailed AS `Mailed Confirm Letter`, 
       r.workbooksetup            AS `Set Up Excel Workbook`, 
       ct.calcteam                AS `Calculation Team`, 
       co.name                    AS `Assigned Staff`, 
       r.reviewdate               AS`Review Date`, 
       bs.statusreported          AS`BoardStatusID`, 
       r.reciprocityid            AS `Reciprocity?`, 
       r.employercodeid           AS `Employer`, 
       d.departmentname           AS `Department`, 
       r.pendingpurchase          AS `Pending Purchase(s)?`, 
       j.joinder                  AS `Joinder or Pending DRO?`, 
       dp.agendadisability        AS `DisabilityID`, 
       ry.reciprocalstatus, 
       o.orientationstatus        AS `Orientation Elected?`, 
       r.orientationdate          AS `Orientation Date`, 
       rp.reviewername            AS `Designated Reviewer`, 
       r.filesetupapproved        AS `File Set-Up Approved`, 
       r.agendaapplication        AS `Agenda Application`, 
       r.estimatetoreviewer       AS `Estimate to Reviewer`, 
       r.estimatetosupervisor     AS `Estimate to Supervisor`, 
       r.estimateapproved         AS `Estimate Approved`, 
       r.finalpaycheck            AS `Final Paycheck Date`, 
       r.finalservice             AS `Final Service with PS/AC`, 
       r.finaltoreviewer          AS `Final Calc to Reviewer`, 
       r.finaltosupervisor        AS `Final Calc to Supervisor`, 
       r.finalapproved            AS `Final Calc Approved`, 
       r.applicationcancelled     AS `App Cancelled by Member?`, 
       r.retelectiondistributed   AS `Retirement Election Distributed`, 
       r.retelectionreturned      AS `Retirement Election Returned`, 
       p.agendaoption             AS `Option/Payment Selected`, 
       r.tempannuityid            AS `Age Request for Temp Annuity`, 
       r.finalallowance           AS `Final Allowance Calculation`, 
       r.continuance              AS `Continuance`, 
       r.payrollformsstaff        AS `Payroll Forms Completed (Staff)`, 
       r.agendapayment            AS `Option-Payment`, 
       r.payrollformssupervisor   AS `Payroll Forms Reviewed (Supv)`, 
       r.cboapprovedallowance     AS `Allowance Approved (CBO)`, 
       r.allowanceestimated       AS `Allowance Estimated?`, 
       r.allowenteredinpayroll    AS `Allowance Entered In Payroll`, 
       r.distributioncycleid      AS `Distribution Cycle for 1st Payment`, 
       r.firstpaydate             AS `Distribution Date for 1st Payment`, 
       r.allowancefinalized       AS `Allowance Finalized`, 
       r.fileimaged               AS `Retirement File Imaged` 
FROM   ((((((((((((tblretirements r 
                   LEFT JOIN tblcalculationpersonnel cp 
                          ON r.coordinatorid = co.coordinatorid) 
                  LEFT JOIN tblcurrentplan cr 
                         ON r.currentplanid = cr.currentplanid) 
                 LEFT JOIN tblbenefitinprocress bp 
                        ON r.benefitinprocess = bp.retirementtypeid) 
                LEFT JOIN tblpaymentoption p 
                       ON r.optionid = p.optionid) 
               LEFT JOIN tblreviewpersonnel rp 
                      ON r.reviewerid = rp.reviewerid) 
              LEFT JOIN tblorientationstatus o 
                     ON r.orientationid = o.orientationid) 
             LEFT JOIN tbldisabilitypending dp 
                    ON r.disabilityid = dp.disabilityid) 
            LEFT JOIN tbljoinder j 
                   ON r.joinderid = j.joinderid) 
           LEFT JOIN tbldepartment dt 
                  ON r.departmentid = d.departmentid) 
          LEFT JOIN tblemployercode e 
                 ON r.employercodeid = e.employercodeid) 
         LEFT JOIN tblreciprocity ry 
                ON r.reciprocityid = ry.reciprocityid) 
        LEFT JOIN tblcalculationteam ct 
               ON r.calculationteamid = ct.calculationteamid) 
       LEFT JOIN tblboardstatus bs 
              ON r.boardstatusid = bs.boardstatusid 

WHERE clause

WHERE r.applicationcancelled = 'No' 
  AND r.receiptdate IS NULL 
   OR r.receiptdate BETWEEN (
                             IIF(MONTH(DATE()) < 7, 
                                 DATESERIAL(YEAR(DATE()) - 1, 7, 1), 
                                 DATESERIAL(YEAR(DATE()), 7, 1)) 
                            )
                        AND (
                             IIF(MONTH(DATE()) < 7, 
                                 DATESERIAL(YEAR(DATE()), 6, 30), 
                                 DATESERIAL(YEAR(DATE()) + 1, 6, 30)) 
                            )

Answer 2

IS not clear why you are using case in where but if you want use in between should be

    " WHERE tblretirements.ApplicationCancelled = 'No' 
      AND (tblretirements.ReceiptDate IS NULL OR tblretirements.ReceiptDate BETWEEN 
      ( CASE WHEN MONTH(getdate()) < 7 THEN DATEFROMPARTS(YEAR(getdate())-1,7,1)
           ELSE DATEFROMPARTS(YEAR(getdate()),7,1)
    End )
     AND  (
    CASE
           WHEN MONTH(getdate()) < 7 THEN DATEFROMPARTS(YEAR(getdate()),6,30)
           ELSE DATEFROMPARTS(YEAR(getdate())+1,6,30)
    End ) )   "