SQL code won't run when CASE function is inserted?

Question

I am trying to fit a CASE function into some SQL code and can't get it to run. Everything has worked perfectly up until the CASE function, and whenever that is inserted, I get a debug error. I was assisted in making this program so I'm hoping it's just a simple error that I'm ignorant of. This is pulling select content from 2 large excel tables based on meeting the criteria. The CASE function is determining whether a value is > or < 12000 and pumping out content based on which content matches that criteria. THANKS!!

See code below:

Dim fPath As String
    Dim oConn As New ADODB.Connection
    Dim oRS As New ADODB.Recordset
    Dim sPath As String, rng1 As Range, rng2 As Range, SQL As String
    
    fPath = ThisWorkbook.FullName 'workbook must be saved somewhere...
    
    'connect to the workbook on disk
    oConn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
               "DBQ=" & fPath & ";"
       
    'select some records which match between lists (zipccode and subdivision)
    '   and with the entered criteria (price, SQFT)
    SQL = " select n.*, o.* from  [OldList$] o, [NewList$] n " & _
          " where n.zipcode = o.zipcode and left(n.Subdivision, 6) = left(o.Subdivision, 6)" & _
          " and (n.listprice - o.listprice) between " & shtCriteria.Range("C4").Value & " and " & shtCriteria.Range("D4").Value & _
          " and (n.sqft - o.sqft) between " & shtCriteria.Range("C6").Value & " and " & shtCriteria.Range("D6").Value & _
          " and CASE " & _
          "         WHEN n.lot > 12000 " & _
          "             THEN o.lot between n.lot + n.lot* " & shtCriteria.Range("C8").Value & " and n.lot + n.lot* " & shtCriteria.Range("D8").Value & " " & _
          "         ELSE (n.lot - o.lot) between " & shtCriteria.Range("C9").Value & " and " & shtCriteria.Range("D9").Value & _
          "     END "
        

            
    Set oRS = oConn.Execute(SQL) 'run the query

Answer 1

CASE returns different results of the same type depending on conditions. It’s not for executing alternate code blocks as you have attempted.

Remove CASE and use OR and AND.

You have used it like this:

WHERE …
AND CASE
      WHEN n.lot > 12000
        THEN <condition 1>
      ELSE <condition 2>
    END

Change it to:

WHERE …
AND (
    (n.lot > 12000 AND <condition 1>)
    OR
    (n.lot <= 12000 AND <condition 2>)
)