Getting a union query to work

[cer5yc]

I have the following union query - they all work seperatly, they all work if they are only joined with one of the other queries. When I put them all together they won't work and I can't figure out why. I keep getting an error that says "The Microsoft Office Access Database Engine could not find the object". Someone please help!!!
SELECT Data.[Primary LOB], Data.[Project Name], Data.EstimatedSpend, Data.TermofContract, Data.Commodity, Data.Strategy, Data.[Procurement Lead], Data.LegalLead, Data.[Primary Business Owner], Data.[Secondary Business Owner], Data.DateReceivedinProcurement, Data.[Date Contract Sent to Vendor], Data.[Date to Legal for Signature], Data.[Date E-copy to Primary BO], Data.ProjectStatus, [Supplier Information].[Supplier Name], Data.WorkflowStatus, Data.[BC#], Data.DateClosed AS [Date], Data.[Tax ID]
FROM [Supplier Information] RIGHT JOIN Data ON [Supplier Information].[Tax ID] = Data.[Tax ID]
WHERE (((Data.ProjectStatus)="2") AND ((Data.DateClosed)>=[Enter Closed Report Start Date] And (Data.DateClosed)<=[Enter Closed Report End Date]) AND ((Data.[Tax ID]) Is Not Null))
UNION
SELECT Data.[Primary LOB], Data.[Project Name], Data.EstimatedSpend, Data.TermofContract, Data.Commodity, Data.Strategy, Data.[Procurement Lead], Data.LegalLead, Data.[Primary Business Owner], Data.[Secondary Business Owner], Data.DateReceivedinProcurement, Data.[Date Contract Sent to Vendor], Data.[Date to Legal for Signature], Data.[Date E-copy to Primary BO], Data.ProjectStatus, [Supplier Information].[Supplier Name], Data.WorkflowStatus, Data.[BC#], Data.DateOpened AS [Date], Data.[Tax ID]
FROM [Supplier Information] RIGHT JOIN Data ON [Supplier Information].[Tax ID] = Data.[Tax ID]
WHERE (((Data.ProjectStatus)="1") AND ((Data.[Tax ID]) Is Not Null));
UNION
SELECT Data.[Primary LOB], Data.[Project Name], Data.EstimatedSpend, Data.TermofContract, Data.Commodity, Data.Strategy, Data.[Procurement Lead], Data.LegalLead, Data.[Primary Business Owner], Data.[Secondary Business Owner], Data.DateReceivedinProcurement, Data.[Date Contract Sent to Vendor], Data.[Date to Legal for Signature], Data.[Date E-copy to Primary BO], Data.ProjectStatus, Data.SupplierName, Data.WorkflowStatus, Data.[BC#], Data.DateOpened AS [Date], Data.[Tax ID]
FROM Data
WHERE (((Data.ProjectStatus)="1") AND ((Data.[Tax ID]) Is Null));

 

[jrbarnett]

Do 1 and 2 work together? Do 2 and 3 work together? do 1 and 3 work together?

ie is it just all three or is one particular query causing problems with the other two in place?

John

 

[cer5yc]

Yes, thus I can't for the life of me figure out why they won't all work together

 

[cer5yc]

Okay to make this even stranger. I used the above union query as the record source for a report. The report pulls just fine and all of the information is correct. Yet I still get an error when I try and run the query from design view. I'm completely baffeled on this one. Anyone have any thoughts on why this would be?

 

[JerryKlmns]

I tried to rewrite your queries into a single one

SELECT 
   D.[Primary LOB], 
   D.[Project Name], 
   D.EstimatedSpend, 
   D.TermofContract, 
   D.Commodity, 
   D.Strategy, 
   D.[Procurement Lead], 
   D.LegalLead, 
   D.[Primary Business Owner], 
   D.[Secondary Business Owner], 
   D.DateReceivedinProcurement, 
   D.[Date Contract Sent to Vendor], 
   D.[Date to Legal for Signature], 
   D.[Date E-copy to Primary BO], 
   D.ProjectStatus, 
   IIF(IsNull(D.[Tax ID]), D.SupplierName, S.[Supplier Name]) As SupplierName, 
   D.WorkflowStatus, 
   D.[BC#], 
   IIF(D.ProjectStatus="2", D.DateClosed, D.DateOpened) AS [Date]
   D.[Tax ID]
FROM [Supplier Information] As S RIGHT JOIN Data As D ON 
     S.[Tax ID] = D.[Tax ID]
WHERE (
       (
        (D.ProjectStatus="2") AND 
        (D.DateClosed>=[Enter Closed Report Start Date] And D.DateClosed<=[Enter Closed Report End Date]) AND 
        (D.[Tax ID] Is Not Null)
       ) OR 
       (D.ProjectStatus="1")
;

Does it return the correct result?

 

[TheAceMan1]

cer5yc . . .

Remove the semicolon at the end of the 2nd SQL . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[JerryKlmns]

John, maybe we should start using a pair of sunglasses like TheAceMan1