okiearcher
New Member
- Joined
- Sep 19, 2014
- Messages
- 1
Hi all.
Newbie poster here.
I have an Access07 Union query that works great and pulls the data just like I need, in Access. When I try to use the query in Excel07 MS Query to pull the data into a sheet in the workbook, I get the error in the title line. Can someone please shed light on why I might be getting this error in MS Query link?
Thanks for your time.
okiearcher
Here's the SQL (in Access):
SELECT [WMNovarprojects].[StoreNbr] & Format([WMNovarprojects].[Sequence],"000") & [WMNovarProjects].[State] AS Project, Sum(WMNovarProjectPOs.QuoteAmount) AS TotPO, WMNovarProjects.SAP_USE_NBR, WMNovarProjects.ProjectPossessionDate AS PossDate
FROM WMNovarProjects INNER JOIN (Quotes INNER JOIN WMNovarProjectPOs ON Quotes.QuotesID = WMNovarProjectPOs.QuotesID) ON (WMNovarProjects.WMProjID = WMNovarProjectPOs.WMProjID) AND (WMNovarProjects.WMProjID = Quotes.WMProjID)
GROUP BY [WMNovarprojects].[StoreNbr] & Format([WMNovarprojects].[Sequence],"000") & [WMNovarProjects].[State], WMNovarProjects.SAP_USE_NBR, WMNovarProjects.ProjectPossessionDate, WMNovarProjectPOs.POCancelled, Quotes.Revised
HAVING ((([WMNovarprojects].[StoreNbr] & Format([WMNovarprojects].[Sequence],"000") & [WMNovarProjects].[State]) Not Like "999*") AND ((WMNovarProjects.ProjectPossessionDate) Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())+2,1,1)-1) AND ((WMNovarProjectPOs.POCancelled)=False) AND ((Quotes.Revised)=False))
ORDER BY [WMNovarprojects].[StoreNbr] & Format([WMNovarprojects].[Sequence],"000") & [WMNovarProjects].[State], WMNovarProjects.ProjectPossessionDate;
UNION SELECT [WMNovarprojects].[StoreNbr] & Format([WMNovarprojects].[Sequence],"000") & [WMNovarProjects].[State] AS Project, Sum(WMNovarProjectPOs.QuoteAmount) AS TotPO, WMNovarProjects.SAP_USE_NBR, WMNovarProjects.ProjectPossessionDate
FROM WMNovarProjects INNER JOIN WMNovarProjectPOs ON WMNovarProjects.WMProjID=WMNovarProjectPOs.WMProjID
GROUP BY [WMNovarprojects].[StoreNbr] & Format([WMNovarprojects].[Sequence],"000") & [WMNovarProjects].[State], WMNovarProjects.SAP_USE_NBR, WMNovarProjects.ProjectType, WMNovarProjects.ProjCreatedBy, WMNovarProjects.ProjectPossessionDate
HAVING (((WMNovarprojects.StoreNbr & Format(WMNovarprojects.Sequence,"000") & WMNovarProjects.State) Not Like "999*") And ((WMNovarProjects.ProjectPossessionDate) Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())+2,1,1)-1));
Newbie poster here.
I have an Access07 Union query that works great and pulls the data just like I need, in Access. When I try to use the query in Excel07 MS Query to pull the data into a sheet in the workbook, I get the error in the title line. Can someone please shed light on why I might be getting this error in MS Query link?
Thanks for your time.
okiearcher
Here's the SQL (in Access):
SELECT [WMNovarprojects].[StoreNbr] & Format([WMNovarprojects].[Sequence],"000") & [WMNovarProjects].[State] AS Project, Sum(WMNovarProjectPOs.QuoteAmount) AS TotPO, WMNovarProjects.SAP_USE_NBR, WMNovarProjects.ProjectPossessionDate AS PossDate
FROM WMNovarProjects INNER JOIN (Quotes INNER JOIN WMNovarProjectPOs ON Quotes.QuotesID = WMNovarProjectPOs.QuotesID) ON (WMNovarProjects.WMProjID = WMNovarProjectPOs.WMProjID) AND (WMNovarProjects.WMProjID = Quotes.WMProjID)
GROUP BY [WMNovarprojects].[StoreNbr] & Format([WMNovarprojects].[Sequence],"000") & [WMNovarProjects].[State], WMNovarProjects.SAP_USE_NBR, WMNovarProjects.ProjectPossessionDate, WMNovarProjectPOs.POCancelled, Quotes.Revised
HAVING ((([WMNovarprojects].[StoreNbr] & Format([WMNovarprojects].[Sequence],"000") & [WMNovarProjects].[State]) Not Like "999*") AND ((WMNovarProjects.ProjectPossessionDate) Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())+2,1,1)-1) AND ((WMNovarProjectPOs.POCancelled)=False) AND ((Quotes.Revised)=False))
ORDER BY [WMNovarprojects].[StoreNbr] & Format([WMNovarprojects].[Sequence],"000") & [WMNovarProjects].[State], WMNovarProjects.ProjectPossessionDate;
UNION SELECT [WMNovarprojects].[StoreNbr] & Format([WMNovarprojects].[Sequence],"000") & [WMNovarProjects].[State] AS Project, Sum(WMNovarProjectPOs.QuoteAmount) AS TotPO, WMNovarProjects.SAP_USE_NBR, WMNovarProjects.ProjectPossessionDate
FROM WMNovarProjects INNER JOIN WMNovarProjectPOs ON WMNovarProjects.WMProjID=WMNovarProjectPOs.WMProjID
GROUP BY [WMNovarprojects].[StoreNbr] & Format([WMNovarprojects].[Sequence],"000") & [WMNovarProjects].[State], WMNovarProjects.SAP_USE_NBR, WMNovarProjects.ProjectType, WMNovarProjects.ProjCreatedBy, WMNovarProjects.ProjectPossessionDate
HAVING (((WMNovarprojects.StoreNbr & Format(WMNovarprojects.Sequence,"000") & WMNovarProjects.State) Not Like "999*") And ((WMNovarProjects.ProjectPossessionDate) Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())+2,1,1)-1));
Last edited: