Query too complex error

[kaledev]

I noticed a strange problem with a query I am trying to contruct. I have a Union query that runs perfectly fine...however if I made a second query that uses it, everything returns a "Query too complex error". So let me try to explain... The first query is below.

"Q01 Generate Data With Duplicates":

SELECT Format([dbo_OLDDATA].[Report Dt],"yyyy-mm-dd") as [Report Dt], [dbo_OLDDATA].[Report Tm], [dbo_OLDDATA].Controller, [dbo_OLDDATA].[Eq Init], [dbo_OLDDATA].[Equip Nr], [dbo_OLDDATA].[Dwell Start Dt], [dbo_OLDDATA].[Dwell Start Tm], [dbo_OLDDATA].[Dwell Start Event], [dbo_OLDDATA].[Dwell Days], [dbo_OLDDATA].Terminal, [dbo_OLDDATA].[Wb Origin], [dbo_OLDDATA].[Wb Destination], [dbo_OLDDATA].[Wb Route]
FROM [dbo_OLDDATA]
WHERE ((([dbo_OLDDATA].[Report Dt]) Between CDate([Forms]![Main]![txtReportStartDate]) And CDate([Forms]![Main]![txtReportEndDate]))) AND [Q01 Generate Data with Duplicates].[Controller] = [Controller];
UNION ALL SELECT [MISSED_DATES].[Missed date] AS [Report Dt], [dbo_OLDDATA].[Report Tm], [dbo_OLDDATA].Controller, [dbo_OLDDATA].[Eq Init], [dbo_OLDDATA].[Equip Nr], [dbo_OLDDATA].[Dwell Start Dt], [dbo_OLDDATA].[Dwell Start Tm], [dbo_OLDDATA].[Dwell Start Event], [dbo_OLDDATA].[Dwell Days], [dbo_OLDDATA].Terminal, [dbo_OLDDATA].[Wb Origin], [dbo_OLDDATA].[Wb Destination], [dbo_OLDDATA].[Wb Route]
FROM [MISSED_DATES] INNER JOIN [dbo_OLDDATA] ON [MISSED_DATES].[Duplicate From] = [dbo_OLDDATA].[Report Dt]
WHERE ((([MISSED_DATES].[Missed date]) Between CDate([Forms]![Main]![txtReportStartDate]) And CDate([Forms]![Main]![txtReportEndDate])));

The query above runs great....now if I create a second query as listed below. It returns the "too complex" error.

SELECT [Q01 Generate Data with Duplicates].[Report Dt]
FROM [Q01 Generate Data with Duplicates];

I have tried every field, and it returns the same error. The first SQL statement is pulling from a SQL Server DB, but like I said it works fine alone so I am not sure if that has anything to do with it. Any ideas? Thanks!

 

[dhruvendra]

It seems the Query is not well formed after "UNION ALL SELECT [MISSED_DATES]......"
So I will say that this should well form the query.

Another thing I will say here is that your query is too large .. an you make it little bit simple to get your exact error..