Help with Syntax error

[aaronbrettuk]

Hi All

Can someone help me find the Syntax error in this SQL please? It's in MS Access - Syntax Error (missing operator) in query expression

SELECT O.offset,
       O.monthenddate,
       R.id
       AS RoleID,
       R.roletitle,
       R.grade,
       R.costcentreid,
       CC.costcentrename,
       CC.costcentreowner,
       ER.id
       AS EmpRoleID,
       ER.payrollid,
       ER.startdate
       AS RoleStartDate,
       "rolestatustext"
       AS RoleStatus,
       "roletypetext"
       AS RoleType,
       ER.enddate
       AS RoleEndDate,
       ER.employmenttype,
       ER.weeklyhours,
       ER.weeklyhours / 37.5
       AS FTE,
       E.id,
       E.firstname,
       E.lastname,
       EP.employercontr,
       ( QSL.newsalary * ( 1 + Getfuturepayrisepct(O.monthenddate) / 100 ) ) /
       12 AS
       CurrentSalary,
       G.bonus
       AS GradeBonusPct,
       (SELECT TOP 1 bonuspayout
        FROM   tblbonuspayout AS BP
        WHERE  BP.bonusyear <= Getfiscalyear(O.monthenddate)
        ORDER  BY BP.bonusyear DESC)
       AS BonusPayoutPct,
       QSL.newsalary * ( 1 + Getfuturepayrisepct(O.monthenddate) / 100 ) / 12 *
       G.bonus
       / 100 * (SELECT TOP 1 bonuspayout
                FROM   tblbonuspayout AS BP2
                WHERE  BP2.bonusyear <= Getfiscalyear(O.monthenddate)
                ORDER  BY BP2.bonusyear DESC) / 100
       AS BonusAmount,
       (SELECT TOP 1 niweeklypaycap
        FROM   tblnirates AS NIcap
        WHERE  NIcap.nieffectivedate <= O.monthenddate
        ORDER  BY NIcap.nieffectivedate DESC) * 52 / 12
       AS MonthlyPayCap,
       (SELECT TOP 1 niemployerrate
        FROM   tblnirates AS NIrate
        WHERE  NIrate.nieffectivedate <= O.monthenddate
        ORDER  BY NIrate.nieffectivedate DESC)
       AS NIEmployerRate,
       Iif(QSL.newsalary / 12 > (SELECT TOP 1 niweeklypaycap
                                 FROM   tblnirates AS NIcap2
                                 WHERE  NIcap2.nieffectivedate <= O.monthenddate
                                 ORDER  BY NIcap2.nieffectivedate DESC) * 52 /
                                12, (
       QSL.newsalary / 12 - (SELECT TOP 1 niweeklypaycap
                             FROM   tblnirates AS NIcap3
                             WHERE  NIcap3.nieffectivedate <= O.monthenddate
                             ORDER  BY NIcap3.nieffectivedate DESC) * 52 / 12 )
                                    * (
       SELECT TOP 1 niemployerrate
       FROM   tblnirates AS NIrate2
       WHERE  NIrate2.nieffectivedate <= O.monthenddate
       ORDER  BY NIrate2.nieffectivedate DESC) / 100, 0)
       AS EmployerNI
FROM   (SELECT offset,
               Dateserial(Year(Dateadd("m", offset, Date())),
               Month(Dateadd("m", offset, Date())) + 1, 0) AS MonthEndDate
        FROM   tbloffsets) AS O
       LEFT JOIN tblemployeeroles AS ER
              ON Nz(ER.startdate, Getnewstarterdate()) <= O.monthenddate
                 AND Nz(ER.enddate, #9999 / 12 / 31 # ) >= O.monthenddate
       LEFT JOIN tblroles AS R
              ON ER.roleid = R.id
       LEFT JOIN tblcostcentre AS CC
              ON R.costcentreid = CC.costcentreid
       LEFT JOIN tblemployees AS E
              ON ER.payrollid = E.payrollid
       LEFT JOIN tblemployerpension AS EP
              ON EP.employeecontr = E.pensioncontr
       LEFT JOIN qrylatestsalarybydate AS QSL
              ON ER.roleid = QSL.roleid
       LEFT JOIN tblgrades AS G
              ON G.grade = R.grade
ORDER  BY O.offset;

 

[Minty]

What's the error being reported as ? A clue would help.
Also can you edit that and put it in code tags (Press </> option in the editor and paste it in )

Edit : if you can Indent it and format it a bit better it might be more readable within the code tags as well.

 

[theDBguy]

Hi. Welcome to AWF!

Copy and paste that SQL into the query designer and post a screenshot of the message you get.

 

[aaronbrettuk]

Hi. Welcome to AWF!

Copy and paste that SQL into the query designer and post a screenshot of the message you get.

Thanks - which query designer?

 

[Gasman]

Thanks - which query designer?

The Access one?

 

[Gasman]

I am going to guess you have missing ( for a SELECT TOP 1 and also missing closing ) from the last SELECT ?

 

[aaronbrettuk]

I am going to guess you have missing ( for a SELECT TOP 1 and also missing closing ) from the last SELECT ?

 

[cheekybuddha]

From the most cursory of glances, I think you will have to nest all those joins, or more likely make intermediate saved queries joining a couple of tables/queries at a time.

 

[The_Doc_Man]

Syntax Error (missing operator) in query expression

In this area of your query, which I slightly reformatted as to line breaks for better readability,...

       AS NIEmployerRate,
       Iif( QSL.newsalary / 12 > 
                                (SELECT TOP 1 niweeklypaycap
                                 FROM   tblnirates AS NIcap2
                                 WHERE  NIcap2.nieffectivedate <= O.monthenddate
                                 ORDER  BY NIcap2.nieffectivedate DESC) * 52 / 12, 
                                (QSL.newsalary / 12 - 
                                        (SELECT TOP 1 niweeklypaycap
                                        FROM   tblnirates AS NIcap3
                                       WHERE  NIcap3.nieffectivedate <= O.monthenddate
                                       ORDER  BY NIcap3.nieffectivedate DESC) * 52 / 12 ) * 
                                      (SELECT TOP 1 niemployerrate
                                           FROM   tblnirates AS> NIrate2
                                          WHERE  NIrate2.nieffectivedate <= O.monthenddate
                                          ORDER  BY NIrate2.nieffectivedate DESC) / 100, 
                                   0)
       AS EmployerNI
FROM

I'm thinking you are missing a parenthesis in here somewhere and as a result you have an improperly constructed expression. I ran a parentheses balance. I think the parenthesis you are missing is a "close parenthese" or ")" but there is no way for me to tell exactly where. But I THINK (guess?) it is in the "true-expression" part of the IIF.

 

[cheekybuddha]

@The_Doc_Man , you may have to tidy up your pasted code

 

[CJ_London]

O.monthenddate

What is this?

 

[The_Doc_Man]

@The_Doc_Man , you may have to tidy up your pasted code

Did clean it up. When I pasted it, those <span>...</span> markers permeated the area. I had never seen that particular marker before and wasn't expecting it. The OP's pasted code didn't show it but my copy/paste of that code DID show it. Decidedly a new experience. Next time I'll copy that to text file and do some find/replace operations.