I have a query that I have created as a Select Query. It will work fine for a little bit, but keeps converting to a Union Query on its own and then gives an error "Syntax Error (missing operator) in query expression 'Tanks.'."
This is driving me nuts. I've gone through the Design View when it is still a Select Query, but can't find any errors. I don't know sql well, so once it converts, I have no idea where to look or what to look for. Below is the sql from the Union Query. Any help would be greatly appreciated.
SELECT Tanks.chkWty_SalesIssue, Tanks.Job_ID, [Status Names].StatusLevel, Tanks.Status, [Project Managers].Initials AS PM, [tanks].[Job Number] & IIf(IsNull([tanks].[Tank Designation]),""," (" & [tanks].[Tank Designation] & ")") AS [Job Number], Nz(Projects.Contractor,Projects.Owner) AS Contractor, [Tanks].[Site_City] & ", " & [Tanks].[Site_State] & IIf([Tanks].[Site_country]="United States",""," " & [Tanks].[Site_country]) AS Location, IIf([Tanks].[Tank Type]="Insulation",[Tanks].[Tank Type],IIf([Tanks].[PartsOnly]=True,[Tanks].[PartsCategory],IIf([Tanks].[Unit]="gal","Liquid","Dry") & " " & [Tanks].[Tank Type])) AS TankType, IIf([tanks].[PartsOnly],[tanks].[PartsCategory],[Tanks].[Diameter] & " x " & [Tanks].[Height]) AS TankSize, First([Latest Memo per Tank].MemoText) AS LatestMemo, [Schedule Construction].[Construction Notes], Tanks.Insulated, Tanks.Union, Tanks.PrevWage, IIf(IsNull([Tanks].[ad_Ship]),IIf(IsNull([Tanks].[td_Ship]),Null,CDate(Nz([Tanks].[td_Ship]))),CDate([Tanks].[ad_Ship])) AS SOD, Tanks.ad_Ship, IIf([Schedule Construction].[Foreman] Is Null,1,0) AS NullSorterForeman, [Schedule Construction].Foreman, IIf([Schedule Construction].[ad_ErectionStart] Is Null,IIf([Schedule Construction].[td_ErectionStart] Is Null,1,0),0) AS NullSorterErectionStart, IIf(IsNull([Schedule Construction].[ad_ErectionStart]),IIf(IsNull([Schedule Construction].[td_ErectionStart]),Null,CDate([Schedule Construction].[td_ErectionStart])),CDate([Schedule Construction].[ad_ErectionStart])) AS ErectionStart, [Schedule Construction].ad_ErectionStart, IIf(IsNull([Schedule Construction].[ad_ErectionFinish]),[Schedule Construction].[td_ErectionFinish],[Schedule Construction].[ad_ErectionFinish]) AS ErectionFinish, [Schedule Construction].ad_ErectionFinish, IIf(IsNull([tanks].[DropDead]),Null,CDate([tanks].[DropDead])) AS DropDead, IIf([Schedule Construction].[ForemanPunch] Is Null,1,0) AS NullSorterForemanPunch, [Schedule Construction].ForemanPunch, IIf([Schedule Construction].[ad_PunchStart] Is Null,IIf([Schedule Construction].[td_PunchStart] Is Null,1,0),0) AS NullSorterPunchStart, IIf(IsNull([Schedule Construction].[ad_PunchStart]),[Schedule Construction].[td_PunchStart],[Schedule Construction].[ad_PunchStart]) AS PunchStart, [Schedule Construction].ad_PunchStart, IIf(IsNull([Schedule Construction].[ad_PunchFinish]),[Schedule Construction].[td_PunchFinish],[Schedule Construction].[ad_PunchFinish]) AS PunchFinish, [Schedule Construction].ad_PunchFinish FROM ((((((((((Projects INNER JOIN Tanks ON Projects.[Job Number] = Tanks.[Job Number]) LEFT JOIN [Project Managers] ON Projects.[Project Manager] = [Project Managers].[Project Manager]) LEFT JOIN [Status Names] ON Tanks.Status = [Status Names].StatusName) LEFT JOIN [Latest Memo per Tank] ON Tanks.Job_ID = [Latest Memo per Tank].Job_ID) LEFT JOIN [Schedule Construction] ON Tanks.Job_ID = [Schedule Construction].Job_ID) LEFT JOIN [Schedule Insulation] ON Tanks.Job_ID = [Schedule Insulation].Job_ID) LEFT JOIN [Schedule Drafting] ON Tanks.Job_ID = [Schedule Drafting].Job_ID) LEFT JOIN [Schedule Manufacturing] ON Tanks.Job_ID = [Schedule Manufacturing].Job_ID) LEFT JOIN [Submittal Status Names] ON [Schedule Drafting].SubmittalStatus = [Submittal Status Names].StatusName) LEFT JOIN [Latest Revision per Tank] ON Tanks.Job_ID = [Latest Revision per Tank].Job_ID) LEFT JOIN [DesignStatus Names] ON [Schedule Drafting].DesignStatus = [DesignStatus Names].StatusName GROUP BY Tanks.chkWty_SalesIssue, Tanks.Job_ID, [Status Names].StatusLevel, Tanks.Status, [Project Managers].Initials, [tanks].[Job Number] & IIf(IsNull([tanks].[Tank Designation]),""," (" & [tanks].[Tank Designation] & ")"), Nz(Projects.Contractor,Projects.Owner), [Tanks].[Site_City] & ", " & [Tanks].[Site_State] & IIf([Tanks].[Site_country]="United States",""," " & [Tanks].[Site_country]), IIf([Tanks].[Tank Type]="Insulation",[Tanks].[Tank Type],IIf([Tanks].[PartsOnly]=True,[Tanks].[PartsCategory],IIf([Tanks].[Unit]="gal","Liquid","Dry") & " " & [Tanks].[Tank Type])), IIf([tanks].[PartsOnly],[tanks].[PartsCategory],[Tanks].[Diameter] & " x " & [Tanks].[Height]), [Schedule Construction].[Construction Notes], Tanks.Insulated, Tanks.UNION , Tanks.PrevWage, IIf(IsNull([Tanks].[ad_Ship]),IIf(IsNull([Tanks].[td_Ship]),Null,CDate(Nz([Tanks].[td_Ship]))),CDate([Tanks].[ad_Ship])), Tanks.ad_Ship, IIf([Schedule Construction].[Foreman] Is Null,1,0), [Schedule Construction].Foreman, IIf([Schedule Construction].[ad_ErectionStart] Is Null,IIf([Schedule Construction].[td_ErectionStart] Is Null,1,0),0), IIf(IsNull([Schedule Construction].[ad_ErectionStart]),IIf(IsNull([Schedule Construction].[td_ErectionStart]),Null,CDate([Schedule Construction].[td_ErectionStart])),CDate([Schedule Construction].[ad_ErectionStart])), [Schedule Construction].ad_ErectionStart, IIf(IsNull([Schedule Construction].[ad_ErectionFinish]),[Schedule Construction].[td_ErectionFinish],[Schedule Construction].[ad_ErectionFinish]), [Schedule Construction].ad_ErectionFinish, IIf(IsNull([tanks].[DropDead]),Null,CDate([tanks].[DropDead])), IIf([Schedule Construction].[ForemanPunch] Is Null,1,0), [Schedule Construction].ForemanPunch, IIf([Schedule Construction].[ad_PunchStart] Is Null,IIf([Schedule Construction].[td_PunchStart] Is Null,1,0),0), IIf(IsNull([Schedule Construction].[ad_PunchStart]),[Schedule Construction].[td_PunchStart],[Schedule Construction].[ad_PunchStart]), [Schedule Construction].ad_PunchStart, IIf(IsNull([Schedule Construction].[ad_PunchFinish]),[Schedule Construction].[td_PunchFinish],[Schedule Construction].[ad_PunchFinish]), [Schedule Construction].ad_PunchFinish HAVING (((Tanks.chkWty_SalesIssue)=False) And ((IIf(Tanks.[Tank Type]="Insulation",Tanks.[Tank Type],IIf(Tanks.PartsOnly=True,Tanks.PartsCategory,IIf(Tanks.Unit="gal","Liquid","Dry") & " " & Tanks.[Tank Type]))) Not Like "Insulation"))
ORDER BY [Status Names].StatusLevel, [tanks].[Job Number] & IIf(IsNull([tanks].[Tank Designation]),""," (" & [tanks].[Tank Designation] & ")"), Nz(Projects.Contractor,Projects.Owner);
This is driving me nuts. I've gone through the Design View when it is still a Select Query, but can't find any errors. I don't know sql well, so once it converts, I have no idea where to look or what to look for. Below is the sql from the Union Query. Any help would be greatly appreciated.
SELECT Tanks.chkWty_SalesIssue, Tanks.Job_ID, [Status Names].StatusLevel, Tanks.Status, [Project Managers].Initials AS PM, [tanks].[Job Number] & IIf(IsNull([tanks].[Tank Designation]),""," (" & [tanks].[Tank Designation] & ")") AS [Job Number], Nz(Projects.Contractor,Projects.Owner) AS Contractor, [Tanks].[Site_City] & ", " & [Tanks].[Site_State] & IIf([Tanks].[Site_country]="United States",""," " & [Tanks].[Site_country]) AS Location, IIf([Tanks].[Tank Type]="Insulation",[Tanks].[Tank Type],IIf([Tanks].[PartsOnly]=True,[Tanks].[PartsCategory],IIf([Tanks].[Unit]="gal","Liquid","Dry") & " " & [Tanks].[Tank Type])) AS TankType, IIf([tanks].[PartsOnly],[tanks].[PartsCategory],[Tanks].[Diameter] & " x " & [Tanks].[Height]) AS TankSize, First([Latest Memo per Tank].MemoText) AS LatestMemo, [Schedule Construction].[Construction Notes], Tanks.Insulated, Tanks.Union, Tanks.PrevWage, IIf(IsNull([Tanks].[ad_Ship]),IIf(IsNull([Tanks].[td_Ship]),Null,CDate(Nz([Tanks].[td_Ship]))),CDate([Tanks].[ad_Ship])) AS SOD, Tanks.ad_Ship, IIf([Schedule Construction].[Foreman] Is Null,1,0) AS NullSorterForeman, [Schedule Construction].Foreman, IIf([Schedule Construction].[ad_ErectionStart] Is Null,IIf([Schedule Construction].[td_ErectionStart] Is Null,1,0),0) AS NullSorterErectionStart, IIf(IsNull([Schedule Construction].[ad_ErectionStart]),IIf(IsNull([Schedule Construction].[td_ErectionStart]),Null,CDate([Schedule Construction].[td_ErectionStart])),CDate([Schedule Construction].[ad_ErectionStart])) AS ErectionStart, [Schedule Construction].ad_ErectionStart, IIf(IsNull([Schedule Construction].[ad_ErectionFinish]),[Schedule Construction].[td_ErectionFinish],[Schedule Construction].[ad_ErectionFinish]) AS ErectionFinish, [Schedule Construction].ad_ErectionFinish, IIf(IsNull([tanks].[DropDead]),Null,CDate([tanks].[DropDead])) AS DropDead, IIf([Schedule Construction].[ForemanPunch] Is Null,1,0) AS NullSorterForemanPunch, [Schedule Construction].ForemanPunch, IIf([Schedule Construction].[ad_PunchStart] Is Null,IIf([Schedule Construction].[td_PunchStart] Is Null,1,0),0) AS NullSorterPunchStart, IIf(IsNull([Schedule Construction].[ad_PunchStart]),[Schedule Construction].[td_PunchStart],[Schedule Construction].[ad_PunchStart]) AS PunchStart, [Schedule Construction].ad_PunchStart, IIf(IsNull([Schedule Construction].[ad_PunchFinish]),[Schedule Construction].[td_PunchFinish],[Schedule Construction].[ad_PunchFinish]) AS PunchFinish, [Schedule Construction].ad_PunchFinish FROM ((((((((((Projects INNER JOIN Tanks ON Projects.[Job Number] = Tanks.[Job Number]) LEFT JOIN [Project Managers] ON Projects.[Project Manager] = [Project Managers].[Project Manager]) LEFT JOIN [Status Names] ON Tanks.Status = [Status Names].StatusName) LEFT JOIN [Latest Memo per Tank] ON Tanks.Job_ID = [Latest Memo per Tank].Job_ID) LEFT JOIN [Schedule Construction] ON Tanks.Job_ID = [Schedule Construction].Job_ID) LEFT JOIN [Schedule Insulation] ON Tanks.Job_ID = [Schedule Insulation].Job_ID) LEFT JOIN [Schedule Drafting] ON Tanks.Job_ID = [Schedule Drafting].Job_ID) LEFT JOIN [Schedule Manufacturing] ON Tanks.Job_ID = [Schedule Manufacturing].Job_ID) LEFT JOIN [Submittal Status Names] ON [Schedule Drafting].SubmittalStatus = [Submittal Status Names].StatusName) LEFT JOIN [Latest Revision per Tank] ON Tanks.Job_ID = [Latest Revision per Tank].Job_ID) LEFT JOIN [DesignStatus Names] ON [Schedule Drafting].DesignStatus = [DesignStatus Names].StatusName GROUP BY Tanks.chkWty_SalesIssue, Tanks.Job_ID, [Status Names].StatusLevel, Tanks.Status, [Project Managers].Initials, [tanks].[Job Number] & IIf(IsNull([tanks].[Tank Designation]),""," (" & [tanks].[Tank Designation] & ")"), Nz(Projects.Contractor,Projects.Owner), [Tanks].[Site_City] & ", " & [Tanks].[Site_State] & IIf([Tanks].[Site_country]="United States",""," " & [Tanks].[Site_country]), IIf([Tanks].[Tank Type]="Insulation",[Tanks].[Tank Type],IIf([Tanks].[PartsOnly]=True,[Tanks].[PartsCategory],IIf([Tanks].[Unit]="gal","Liquid","Dry") & " " & [Tanks].[Tank Type])), IIf([tanks].[PartsOnly],[tanks].[PartsCategory],[Tanks].[Diameter] & " x " & [Tanks].[Height]), [Schedule Construction].[Construction Notes], Tanks.Insulated, Tanks.UNION , Tanks.PrevWage, IIf(IsNull([Tanks].[ad_Ship]),IIf(IsNull([Tanks].[td_Ship]),Null,CDate(Nz([Tanks].[td_Ship]))),CDate([Tanks].[ad_Ship])), Tanks.ad_Ship, IIf([Schedule Construction].[Foreman] Is Null,1,0), [Schedule Construction].Foreman, IIf([Schedule Construction].[ad_ErectionStart] Is Null,IIf([Schedule Construction].[td_ErectionStart] Is Null,1,0),0), IIf(IsNull([Schedule Construction].[ad_ErectionStart]),IIf(IsNull([Schedule Construction].[td_ErectionStart]),Null,CDate([Schedule Construction].[td_ErectionStart])),CDate([Schedule Construction].[ad_ErectionStart])), [Schedule Construction].ad_ErectionStart, IIf(IsNull([Schedule Construction].[ad_ErectionFinish]),[Schedule Construction].[td_ErectionFinish],[Schedule Construction].[ad_ErectionFinish]), [Schedule Construction].ad_ErectionFinish, IIf(IsNull([tanks].[DropDead]),Null,CDate([tanks].[DropDead])), IIf([Schedule Construction].[ForemanPunch] Is Null,1,0), [Schedule Construction].ForemanPunch, IIf([Schedule Construction].[ad_PunchStart] Is Null,IIf([Schedule Construction].[td_PunchStart] Is Null,1,0),0), IIf(IsNull([Schedule Construction].[ad_PunchStart]),[Schedule Construction].[td_PunchStart],[Schedule Construction].[ad_PunchStart]), [Schedule Construction].ad_PunchStart, IIf(IsNull([Schedule Construction].[ad_PunchFinish]),[Schedule Construction].[td_PunchFinish],[Schedule Construction].[ad_PunchFinish]), [Schedule Construction].ad_PunchFinish HAVING (((Tanks.chkWty_SalesIssue)=False) And ((IIf(Tanks.[Tank Type]="Insulation",Tanks.[Tank Type],IIf(Tanks.PartsOnly=True,Tanks.PartsCategory,IIf(Tanks.Unit="gal","Liquid","Dry") & " " & Tanks.[Tank Type]))) Not Like "Insulation"))
ORDER BY [Status Names].StatusLevel, [tanks].[Job Number] & IIf(IsNull([tanks].[Tank Designation]),""," (" & [tanks].[Tank Designation] & ")"), Nz(Projects.Contractor,Projects.Owner);