Select Query keeps changing to Union Query (1 Viewer)

jackh113

New member
Local time
Today, 06:30
Joined
Sep 7, 2015
Messages
9
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);
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:30
Joined
Aug 30, 2003
Messages
36,118
Union is a reserved word, so try bracketing every instance of it. Or even change the name if it's not too late.
 

jackh113

New member
Local time
Today, 06:30
Joined
Sep 7, 2015
Messages
9
That would make complete sense. It just started doing this after I made some changes/additions to the query. [union] being on of the additions. It never even occurred to me. I'll give it a try.

Sometimes it just takes another set of eyes. Thanks for the help.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:30
Joined
Aug 30, 2003
Messages
36,118
No problem; hopefully that resolves the issue.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:30
Joined
Aug 30, 2003
Messages
36,118
Happy to help!
 

Users who are viewing this thread

Top Bottom