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

F

FueledbyAccess

New member
Local time
Today, 16:45
Joined
Sep 6, 2019
Messages
6
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

Gasman

Enthusiastic Amateur
Local time
Today, 16:45
Joined
Sep 21, 2011
Messages
14,238
Oh My :(

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

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:45
Joined
May 21, 2018
Messages
8,525
SQL: 
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

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:45
Joined
May 21, 2018
Messages
8,525
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
Code: 
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

SHANEMAC51

Active member
Local time
Today, 18:45
Joined
Jan 28, 2022
Messages
310
Code: 
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

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:45
Joined
May 21, 2018
Messages
8,525
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

GPGeorge

Grover Park George
Local time
Today, 08:45
Joined
Nov 25, 2004
Messages
1,831
MajP said:
SQL: 
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;
Click to expand...
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.
 
F

FueledbyAccess

New member
Local time
Today, 16:45
Joined
Sep 6, 2019
Messages
6
SHANEMAC51 said:
Code: 
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,
Click to expand...
Thank you. Made these changes and it worked.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:45
Joined
Feb 19, 2002
Messages
43,233
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.
 
You must log in or register to reply here.

Similar threads

F
Solved Left Join on Union Query not working
Replies
5
Views
1,245
theDBguy
theDBguy
M
Run-time error '3075': Syntax error (missing operator) in Access 2013 but not in 2010
Replies
9
Views
1,787
theDBguy
theDBguy
isladogs
  • Locked
Show Plan - Run Faster
Replies
3
Views
3,291
isladogs
isladogs
C
Problem with date parameter in query by form in Access 2003
Replies
0
Views
2,272
Cris
C
S
Rows repeat in a query but the data is not duplicate data
Replies
5
Views
3,581
MSAccessRookie
MSAccessRookie

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom