none
"Syntax error (missing operator)" when trying to switch from SQL to Design View

    Question

  • Hey everyone,

    I've been working on a CrossTab query and gave up on the design view to just try coding it in SQL view. When I try to switch back to design view I keep getting  a Syntax error:

    Syntax error (missing operator) in Query expression SUM(CASE WHEN [AwardsIssued (Summary)].[Jan] IS NOT NULL THEN [AwardsIssued (Summary)].[Jan] ELSE 0 END)'.

    I'm sure this is probably a pretty easy fix. I'm fairly new to this. My code is as follows:

    TRANSFORM Count([AwardsIssued (Summary)].EmployeeNumber) AS CountOfEmployeeNumber
    SELECT [Employees -ref].CostCenter, [AwardsIssued (Summary)].[Employee Name],
    SUM(CASE WHEN [AwardsIssued (Summary)].[Jan] IS NOT NULL THEN [AwardsIssued (Summary)].[Jan] ELSE 0 END) AS IssuedJan,
    SUM(CASE WHEN [AwardsIssued (Summary)].[Feb] IS NOT NULL THEN [AwardsIssued (Summary)].[Feb] ELSE 0 END) AS IssuedFeb,
    SUM(CASE WHEN [AwardsIssued (Summary)].[Mar] IS NOT NULL THEN [AwardsIssued (Summary)].[Mar] ELSE 0 END) AS IssuedMar,
    SUM(CASE WHEN [AwardsIssued (Summary)].[Apr] IS NOT NULL THEN [AwardsIssued (Summary)].[Apr] ELSE 0 END) AS IssuedApr,
    SUM(CASE WHEN [AwardsIssued (Summary)].[May] IS NOT NULL THEN [AwardsIssued (Summary)].[May] ELSE 0 END) AS IssuedMay, 
    SUM(CASE WHEN [AwardsIssued (Summary)].[Jun] IS NOT NULL THEN [AwardsIssued (Summary)].[Jun] ELSE 0 END) AS IssuedJun,
    SUM(CASE WHEN [AwardsIssued (Summary)].[Jul] IS NOT NULL THEN [AwardsIssued (Summary)].[Jul] ELSE 0 END) AS IssuedJul,
    SUM(CASE WHEN [AwardsIssued (Summary)].[Aug] IS NOT NULL THEN [AwardsIssued (Summary)].[Aug] ELSE 0 END) AS IssuedAug,
    SUM(CASE WHEN [AwardsIssued (Summary)].[Sep] IS NOT NULL THEN [AwardsIssued (Summary)].[Sep] ELSE 0 END) AS IssuedSep,
    SUM(CASE WHEN [AwardsIssued (Summary)].[Oct] IS NOT NULL THEN [AwardsIssued (Summary)].[Oct] ELSE 0 END) AS IssuedOct,
    SUM(CASE WHEN [AwardsIssued (Summary)].[Nov] IS NOT NULL THEN [AwardsIssued (Summary)].[Nov] ELSE 0 END) AS IssuedNov,
    SUM(CASE WHEN [AwardsIssued (Summary)].[Dec] IS NOT NULL THEN [AwardsIssued (Summary)].[Dec] ELSE 0 END) AS IssuedDec
    FROM [Employees -ref] INNER JOIN [AwardsIssued (Summary)] ON [Employees -ref].EmployeeNumber = [AwardsIssued (Summary)].EmployeeNumber
    GROUP BY [Employees -ref].CostCenter, [AwardsIssued (Summary)].[Employee Name]
    PIVOT [AwardsIssued (Summary)].Type;

    Thanks in advance.

     

     


    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    Wednesday, March 16, 2011 6:11 PM

Answers

  • CASE WHEN is T-SQL, the SQL dialect used in SQL Server. It is not valid Access SQL.

    You can replace

    CASE WHEN [AwardsIssued (Summary)].[Jan] IS NOT NULL THEN [AwardsIssued (Summary)].[Jan] ELSE 0 END

    with

    Nz([AwardsIssued (Summary)].[Jan],0)

    etc.


    Regards, Hans Vogelaar
    • Marked as answer by Don Mon Wednesday, March 16, 2011 6:29 PM
    Wednesday, March 16, 2011 6:27 PM

All replies

  • CASE WHEN is T-SQL, the SQL dialect used in SQL Server. It is not valid Access SQL.

    You can replace

    CASE WHEN [AwardsIssued (Summary)].[Jan] IS NOT NULL THEN [AwardsIssued (Summary)].[Jan] ELSE 0 END

    with

    Nz([AwardsIssued (Summary)].[Jan],0)

    etc.


    Regards, Hans Vogelaar
    • Marked as answer by Don Mon Wednesday, March 16, 2011 6:29 PM
    Wednesday, March 16, 2011 6:27 PM
  • Thank you Hans. I knew there had to be a simple answer.


    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    Wednesday, March 16, 2011 6:29 PM
  • I would try:

    SUM(NZ([AwardsIssued (Summary)].[Jan],0))

    or

    SUM(IIF(Not IsNull([AwardsIssued (Summary)].[Jan]),[AwardsIssued (Summary)].[Jan]))

    instead of

    SUM(CASE WHEN [AwardsIssued (Summary)].[Jan] IS NOT NULL THEN [AwardsIssued (Summary)].[Jan] ELSE 0 END)'.


    -- Roger Carlson
    MS Access MVP 2006-2011
    www.rogersaccesslibrary.com
    Wednesday, March 16, 2011 6:32 PM