Microsoft Access Error

Question

Error: Invalid use of IS operator in query expression...

Here is the formatted SQL-Code by poorsql.com

TRANSFORM Sum(Jacksonville.[Lease Rent]) AS SumOfAmount

SELECT S.Unit
    ,S.[Floorplan]
    ,S.[Unit Designation]
    ,S.SQFT
    ,S.[Unit and Lease Status]
    ,S.NAME
    ,S.[Move-In and Move-Out]
    ,S.[Lease Start]
    ,S.[Lease End]
    ,S.[Market and Addl.]
    ,S.[Sub Journal]
    ,S.[Trans Code]
    ,S.[Lease Rent]
    ,S.[Charges and Credits]
FROM Jacksonville
    ,(
        SELECT Q.Id
            ,Val(DMax(“Id”, ”Jacksonville”, ”Id <= “ & Q.[Id] & “
                    AND Unit IS NOT Null”)) AS ParentId
        FROM Jacksonville AS Q
        ) AS T
    ,(
        SELECT Jacksonville.Id
            ,Jacksonville.Unit
            ,Jacksonville.[Floorplan]
            ,Jacksonville.[Unit Designation]
            ,Jacksonville.SQFT
            ,Jacksonville.[Unit and Lease Status]
            ,Jacksonville.NAME
            ,Jacksonville.[MoveIn and MoveOut]
            ,Jacksonville.[Lease Start]
            ,Jacksonville.[Lease End]
            ,Jacksonville.[Market and Addl]
            ,Jacksonville.[Sub Journal]
            ,Jacksonville.[Trans Code]
            ,Jacksonville.[Lease Rent]
            ,Jacksonville.[Charges and Credits]
        FROM Jacksonville
        WHERE Jacksonville.Unit IS NOT NULL
        ) AS S
WHERE Jacksonville.Id = [T].[Id]
    AND T.ParentId = [S].[Id]
GROUP BY T.ParentId
    ,S.Unit
    ,S.[Floorplan]
    ,S.[Unit Designation]
    ,S.SQFT
    ,S.[Unit and Lease Status]
    ,S.NAME
    ,S.[Move-In and Move-Out]
    ,S.[Lease Start]
    ,S.[Lease End]
    ,S.[Market and Addl.]
    ,S.[Sub Journal]
    ,S.[Trans Code]
    ,S.[Lease Rent]
    ,S.[Charges and Credits]
PIVOT Jacksonville.[Trans Code] IN (
        [“ADMIN HOUS”]
        ,”EXRENT”
        ,[”LHA RENT”]
        ,[”MK-PREMIUM LHA”]
        ,”RENT”
        ,”SUBRENT”
        ,”SUBSIDY”
        ,”UTAC”
        ,”UTILREIMB”
        ,”OFF / SOCIAL”
        );

This syntax works on my other one, but not here for some reason. Why is my IS statement wrong? Not sure how to edit the query structure on here.

Answer 1

Replace the "smart quotes" (from a copy-paste from Word?) with double-quotes:

,Val(DMax("Id", "Jacksonville", "Id <= " & Q.[Id] & "
                AND Unit IS NOT Null")) AS ParentId