Solved Query not running, no idea why :( (1 Viewer)

[FueledbyAccess]

Hello,
I'm hoping someone can help to get my query to run. At work I've inherited a database and now need to add a new year to the query that calculates hourly rates.

Below is the query I've updated, the line I added is highlighted red. However I get a 'Syntax error (missing operator) in query expression' message

SELECT '' AS [Nominal Code], datevalue(t1.Date) AS DayDate, month(t1.Date) AS [Month], '' AS [Header Ref], '' AS [Internal Ref], t2.Resource_ID & ' ' & T2.Name AS Supplier, year( t1.Date) AS [Year], t1.[Project Code] AS Project, t1.[Cost Centre Code] AS Task, t1.ProjectCentreID AS TaskKey, t1.ApprovalStatus AS [Detail Line], datevalue(t1.Date) AS Required, '_Staff' AS OrderNumber, datevalue(t1.Date) AS [Order Date], t2.Resource_ID AS [Entered by], '' AS [Line Number], iif(t3.Portfolio ='OVERHEAD', 0,
iif( DateValue(t1.Date) < datevalue('01/01/2019'), iif(t1.Price = 0, 0, 90),
iif(t2.Type = 'GRADUATE SECONDEE', 0,
iif( t2.team in ('QA AND H&S', 'SC&P-C&S','APPLIED TECH COMMERI') ,0,

switch(
DateValue(t1.Date) < datevalue('01/01/2020'), iif( t4.[User Sort 2] in ('MACHINE SHOP', 'PRODUCTION - HX MANU','SC&P-MANUF OPERATION','SC&P-MACHINISTS'), 65, 80),
DateValue(t1.Date) < datevalue('01/01/2021'), iif( t4.[User Sort 2] in ('MACHINE SHOP', 'PRODUCTION - HX MANU','SC&P-MANUF OPERATION','SC&P-MACHINISTS'), 65, t4.[Resource Price 1]),
DateValue(t1.Date) < datevalue('01/01/2022'), iif( t2.Team in ('MACHINE SHOP', 'PRODUCTION - HX MANU','SC&P-MANUF OPERATION', ‘SC&P-Man Ops & Tech’,'SC&P-TECHNICAL','SC&P-MACHINISTS'), 68, 98),
DateValue(t1.Date) >= datevalue('01/01/2022'), iif( t2.Team in ('MACHINE SHOP', 'PRODUCTION - HX MANU','SC&P-MANUF OPERATION',’ SC&P-Man Ops & Tech’, 'SC&P-TECHNICAL','SC&P-MACHINISTS'), 62, 92)

))))))* t1.Quantity AS Net, t1.Quantity AS Hours, '' AS [Nominal Account Name], 'Labour' AS CostType1, iif((T2.Type)='', 'EMPLOYEE' ,
iif(T2.Type='Contractor',
'SUBCONTRACTOR',T2.Type)) AS Cost_Type2, T2.Team AS Cost_Type3, t2.CostCentre AS GroupCostCentre, iif( t3.[Project Code] = '998','Leave', iif( t3.[Portfolio] = 'OVERHEAD', 'Overhead', 'Productive')) AS Hrs_type, iif( t2.team in ('ADMIN SUPPORT','COMPANY MANAGEMENT','FACILITIES','FINANCE','HR','IT','LEGAL & EC','MARKETING','SUPPLY CHAIN'), 'Support Functions', 'Productive Functions') AS Function_type
FROM ((prep_act_time1 AS t1 LEFT JOIN Resources AS t2 ON t1.[Resource Code] = t2.[Resource Code]) LEFT JOIN 2019_Cost_rates_data AS t4 ON t1.[Resource Code] = t4.[Resource Code]) LEFT JOIN Ref_Project_Task AS t3 ON t1.ProjectCentreID = t3.Key;


This is the original query which works fine
SELECT '' AS [Nominal Code], datevalue(t1.Date) AS DayDate, month(t1.Date) AS [Month], '' AS [Header Ref], '' AS [Internal Ref], t2.Resource_ID & ' ' & T2.Name AS Supplier, year( t1.Date) AS [Year], t1.[Project Code] AS Project, t1.[Cost Centre Code] AS Task, t1.ProjectCentreID AS TaskKey, t1.ApprovalStatus AS [Detail Line], datevalue(t1.Date) AS Required, '_Staff' AS OrderNumber, datevalue(t1.Date) AS [Order Date], t2.Resource_ID AS [Entered by], '' AS [Line Number], iif(t3.Portfolio ='OVERHEAD', 0,
iif( DateValue(t1.Date) < datevalue('01/01/2019'), iif(t1.Price = 0, 0, 90),
iif(t2.Type = 'GRADUATE SECONDEE', 0,
iif( t2.team in ('QA AND H&S', 'SC&P-C&S','APPLIED TECH COMMERI') ,0,

switch(
DateValue(t1.Date) < datevalue('01/01/2020'), iif( t4.[User Sort 2] in ('MACHINE SHOP', 'PRODUCTION - HX MANU','SC&P-MANUF OPERATION','SC&P-MACHINISTS'), 65, 80),
DateValue(t1.Date) < datevalue('01/01/2021'), iif( t4.[User Sort 2] in ('MACHINE SHOP', 'PRODUCTION - HX MANU','SC&P-MANUF OPERATION','SC&P-MACHINISTS'), 65, t4.[Resource Price 1]),
DateValue(t1.Date) >= datevalue('01/01/2021'), iif( t2.Team in ('MACHINE SHOP', 'PRODUCTION - HX MANU','SC&P-MANUF OPERATION', 'SC&P-TECHNICAL','SC&P-MACHINISTS'), 68, 98)
)))))* t1.Quantity AS Net, t1.Quantity AS Hours, '' AS [Nominal Account Name], 'Labour' AS CostType1, iif((T2.Type)='', 'EMPLOYEE' ,
iif(T2.Type='Contractor',
'SUBCONTRACTOR',T2.Type)) AS Cost_Type2, T2.Team AS Cost_Type3, t2.CostCentre AS GroupCostCentre, iif( t3.[Project Code] = '998','Leave', iif( t3.[Portfolio] = 'OVERHEAD', 'Overhead', 'Productive')) AS Hrs_type, iif( t2.team in ('ADMIN SUPPORT','COMPANY MANAGEMENT','FACILITIES','FINANCE','HR','IT','LEGAL & EC','MARKETING','SUPPLY CHAIN'), 'Support Functions', 'Productive Functions') AS Function_type
FROM ((prep_act_time1 AS t1 LEFT JOIN Resources AS t2 ON t1.[Resource Code] = t2.[Resource Code]) LEFT JOIN 2019_Cost_rates_data AS t4 ON t1.[Resource Code] = t4.[Resource Code]) LEFT JOIN Ref_Project_Task AS t3 ON t1.ProjectCentreID = t3.Key;

 

[Gasman]

Oh My

Put it all into the QBE window and see it it highlights the error?

 

[MajP]

SELECT ''                             AS [Nominal Code],
       Datevalue(t1.date)             AS daydate,
       Month(t1.date)                 AS [MONTH],
       ''                             AS [Header Ref],
       ''                             AS [Internal Ref],
       t2.resource_id & ' ' & t2.NAME AS supplier,
       Year( t1.date)                 AS [YEAR],
       t1.[Project Code]              AS project,
       t1.[Cost Centre Code]          AS task,
       t1.projectcentreid             AS taskkey,
       t1.approvalstatus              AS [Detail Line],
       Datevalue(t1.date)             AS required,
       '_Staff'                       AS ordernumber,
       Datevalue(t1.date)             AS [ORDER DATE],
       t2.resource_id                 AS [Entered BY],
       ''                             AS [Line NUMBER],
       iif( t3.portfolio ='OVERHEAD', 0, iif( Datevalue(t1.date) < Datevalue('01/01/2019'), Iif( t1.price = 0, 0, 90), iif( t2.type = 'GRADUATE SECONDEE', 0, iif( t2.team IN ('QA AND H&S',
                                                                                                                                                                               'SC&P-C&S',
                                                                                                                                                                               'APPLIED TECH COMMERI') ,0, switch( Datevalue(t1.date) < Datevalue('01/01/2020'), Iif( t4.[USER Sort 2] IN ('MACHINE SHOP',
                                                                                                                                                                                                        'PRODUCTION - HX MANU',
                                                                                                                                                                                                        'SC&P-MANUF OPERATION',
                                                                                                                                                                                                        'SC&P-MACHINISTS'), 65, 80), Datevalue(t1.date) < Datevalue('01/01/2021'), Iif( t4.[USER Sort 2] IN ('MACHINE SHOP',
                                                                                                                                                                                                        'PRODUCTION - HX MANU',
                                                                                                                                                                                                        'SC&P-MANUF OPERATION',
                                                                                                                                                                                                        'SC&P-MACHINISTS'), 65, t4.[RESOURCE Price 1]), Datevalue(t1.date) < Datevalue('01/01/2022'), iif( t2.team IN ('MACHINE SHOP',
                                                                                                                                                                                                        'PRODUCTION - HX MANU',
                                                                                                                                                                                                        'SC&P-MANUF OPERATION',
                                                                                                                                                                                                        ‘sc&p-man ops & tech’,
                                                                                                                                                                                                        'SC&P-TECHNICAL',
                                                                                                                                                                                                        'SC&P-MACHINISTS'), 68, 98), datevalue(t1.date) >= datevalue('01/01/2022'), iif( t2.team IN ('MACHINE SHOP',
                                                                                                                                                                                                        'PRODUCTION - HX MANU',
                                                                                                                                                                                                        'SC&P-MANUF OPERATION',
                                                                                                                                                                                                        ’ sc&p-man ops & tech’,
                                                                                                                                                                                                        'SC&P-TECHNICAL',
                                                                                                                                                                                                        'SC&P-MACHINISTS'), 62, 92) ))))))* t1.quantity AS net, t1.quantity AS hours, '' AS [Nominal Account Name], 'Labour' AS costtype1, iif((t2.type)='', 'EMPLOYEE' , iif(t2.type='Contractor', 'SUBCONTRACTOR',t2.type)) AS cost_type2,
t2.team AS                                                                                                                                                                                                        cost_type3,
t2.costcentre AS                                                                                                                                                                                                        groupcostcentre,
iif( t3.[Project Code] = '998','Leave', iif( t3.[Portfolio] = 'OVERHEAD', 'Overhead', 'Productive')) AS                                                                                                                                                                                                        hrs_type,
iif( t2.team IN ('ADMIN SUPPORT',
                 'COMPANY MANAGEMENT',
                 'FACILITIES',
                 'FINANCE',
                 'HR',
                 'IT',
                 'LEGAL & EC',
                 'MARKETING',
                 'SUPPLY CHAIN'), 'Support Functions', 'Productive Functions') AS function_type FROM ((prep_act_time1 AS t1 LEFT JOIN resources AS t2 ON t1.[RESOURCE Code] = t2.[RESOURCE Code]) LEFT JOIN 2019_cost_rates_data AS t4 ON t1.[RESOURCE Code] = t4.[RESOURCE Code]) LEFT JOIN ref_project_task AS t3 ON t1.projectcentreid = t3.KEY;

 

[MajP]

I doubt anyone is going to spend their time on those Nested iifs, switch functions. that looks like a PITA. Often when I see a bunch of nested iifs it can be helped by putting that information into a table and not code. Same as the switch function. Not always
Although iifs are probably more efficient than a user defined function. it may be way easier to write a UDF.

I

iif( t3.[Project Code] = '998','Leave', iif( t3.[Portfolio] = 'OVERHEAD', 'Overhead', 'Productive')) AS                                                                                                                                                                                                        hrs_type,
iif( t2.team IN ('ADMIN SUPPORT',
                 'COMPANY MANAGEMENT',
                 'FACILITIES',
                 'FINANCE',
                 'HR',
                 'IT',
                 'LEGAL & EC',
                 'MARKETING',
                 'SUPPLY CHAIN'), 'Support Functions', 'Productive Functions')

that could be a very simple lookup table and a UDF.
Admin Support Support Functions
Company Management Support Functions

. Other Fields Productive Functions

 

[SHANEMAC51]

DateValue(t1.Date) < datevalue('01/01/2022'),
iif( t2.Team in ('MACHINE SHOP', 'PRODUCTION - HX MANU','SC&P-MANUF OPERATION',
‘SC&P-Man Ops & Tech’,'SC&P-TECHNICAL','SC&P-MACHINISTS'), 68, 98)

‘SC&P-Man Ops & Tech’
)))))* t1.Quantity AS Net,

 

[MajP]

I am not suggesting you cannot debug it as written, but trying to get that formatting and logic correct with the nested iifs and switch would be a total PITA. I would not be interested in spending that time. I would look at as many ways I could to simplify. Subqueries, UDF, and lookup tables.

 

[GPGeorge]

SELECT ''                             AS [Nominal Code],
       Datevalue(t1.date)             AS daydate,
       Month(t1.date)                 AS [MONTH],
       ''                             AS [Header Ref],
       ''                             AS [Internal Ref],
       t2.resource_id & ' ' & t2.NAME AS supplier,
       Year( t1.date)                 AS [YEAR],
       t1.[Project Code]              AS project,
       t1.[Cost Centre Code]          AS task,
       t1.projectcentreid             AS taskkey,
       t1.approvalstatus              AS [Detail Line],
       Datevalue(t1.date)             AS required,
       '_Staff'                       AS ordernumber,
       Datevalue(t1.date)             AS [ORDER DATE],
       t2.resource_id                 AS [Entered BY],
       ''                             AS [Line NUMBER],
       iif( t3.portfolio ='OVERHEAD', 0, iif( Datevalue(t1.date) < Datevalue('01/01/2019'), Iif( t1.price = 0, 0, 90), iif( t2.type = 'GRADUATE SECONDEE', 0, iif( t2.team IN ('QA AND H&S',
                                                                                                                                                                               'SC&P-C&S',
                                                                                                                                                                               'APPLIED TECH COMMERI') ,0, switch( Datevalue(t1.date) < Datevalue('01/01/2020'), Iif( t4.[USER Sort 2] IN ('MACHINE SHOP',
                                                                                                                                                                                                        'PRODUCTION - HX MANU',
                                                                                                                                                                                                        'SC&P-MANUF OPERATION',
                                                                                                                                                                                                        'SC&P-MACHINISTS'), 65, 80), Datevalue(t1.date) < Datevalue('01/01/2021'), Iif( t4.[USER Sort 2] IN ('MACHINE SHOP',
                                                                                                                                                                                                        'PRODUCTION - HX MANU',
                                                                                                                                                                                                        'SC&P-MANUF OPERATION',
                                                                                                                                                                                                        'SC&P-MACHINISTS'), 65, t4.[RESOURCE Price 1]), Datevalue(t1.date) < Datevalue('01/01/2022'), iif( t2.team IN ('MACHINE SHOP',
                                                                                                                                                                                                        'PRODUCTION - HX MANU',
                                                                                                                                                                                                        'SC&P-MANUF OPERATION',
                                                                                                                                                                                                        ‘sc&p-man ops & tech’,
                                                                                                                                                                                                        'SC&P-TECHNICAL',
                                                                                                                                                                                                        'SC&P-MACHINISTS'), 68, 98), datevalue(t1.date) >= datevalue('01/01/2022'), iif( t2.team IN ('MACHINE SHOP',
                                                                                                                                                                                                        'PRODUCTION - HX MANU',
                                                                                                                                                                                                        'SC&P-MANUF OPERATION',
                                                                                                                                                                                                        ’ sc&p-man ops & tech’,
                                                                                                                                                                                                        'SC&P-TECHNICAL',
                                                                                                                                                                                                        'SC&P-MACHINISTS'), 62, 92) ))))))* t1.quantity AS net, t1.quantity AS hours, '' AS [Nominal Account Name], 'Labour' AS costtype1, iif((t2.type)='', 'EMPLOYEE' , iif(t2.type='Contractor', 'SUBCONTRACTOR',t2.type)) AS cost_type2,
t2.team AS                                                                                                                                                                                                        cost_type3,
t2.costcentre AS                                                                                                                                                                                                        groupcostcentre,
iif( t3.[Project Code] = '998','Leave', iif( t3.[Portfolio] = 'OVERHEAD', 'Overhead', 'Productive')) AS                                                                                                                                                                                                        hrs_type,
iif( t2.team IN ('ADMIN SUPPORT',
                 'COMPANY MANAGEMENT',
                 'FACILITIES',
                 'FINANCE',
                 'HR',
                 'IT',
                 'LEGAL & EC',
                 'MARKETING',
                 'SUPPLY CHAIN'), 'Support Functions', 'Productive Functions') AS function_type FROM ((prep_act_time1 AS t1 LEFT JOIN resources AS t2 ON t1.[RESOURCE Code] = t2.[RESOURCE Code]) LEFT JOIN 2019_cost_rates_data AS t4 ON t1.[RESOURCE Code] = t4.[RESOURCE Code]) LEFT JOIN ref_project_task AS t3 ON t1.projectcentreid = t3.KEY;

I'm on the same team with MajP here.

This is a prime example of what I call "code-wadding", albeit in SQL as opposed to VBA where we usually see it.

In elementary school kids chewed gum and when told to take it out of their mouths, just added it to the previously chewed wads of gum on the bottom of their desks.

"Code wadding" follows the same approach. Whenever something new appears, we just stick another wad of code on it (add a new member to the Switches and IIfs and so on). Eventually the whole thing just has to be scraped off and discarded.

You inherited this mess, and I suspect it'll take a lot of effort to scrape it away and replace it with a more viable approach, but that's really what needs to be done.

 

[Pat Hartman]

Poor you We'll help you with it though.

 

[FueledbyAccess]

DateValue(t1.Date) < datevalue('01/01/2022'),
iif( t2.Team in ('MACHINE SHOP', 'PRODUCTION - HX MANU','SC&P-MANUF OPERATION',
‘SC&P-Man Ops & Tech’,'SC&P-TECHNICAL','SC&P-MACHINISTS'), 68, 98)

‘SC&P-Man Ops & Tech’
)))))* t1.Quantity AS Net,

Thank you. Made these changes and it worked.

 

[Pat Hartman]

At least you got it to work. If you want the best solution, make a table with the teams and add group values to assign them to groups. If each team can belong to only one group, the process is pretty simple. To pick up the group, join the table with the team name (or preferably TeamID) and join to tblTeam to pick up the group value. Even better, if you already have a team table, just add the group to it.