"Query too complex" message

[Guest]

Hi
I have created a query and named it say qry_MTD_YTD_2006.
I changed this query and it did not work anymore.
I decided to delete it.
Then I made a new query and it worked and I saved it under the default name
of Query1.
Then I decided to rename Query1 as qry_MTD_YTD_2006.
When I tried to run the query "qry_MTD_YTD_2006" I got the message that the
query was too complex. BUT the same query worked under the default name of
Query1.
When I slightly changed the name to qry_M_Y_2006, all of a sudden the query
worked.
This has been happening a lot and it drives me NUTS.
How can I delete a query in such a way that I can reuse the query name at a
later stage?
Thx

 

[Allen Browne]

This sounds suspiciously like the wonderous Name AutoCorrect nightmare that
plagues Access 2000 and later. Fortunately, you can turn it off.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact
Repeat the compact a 2nd time.

That will probably get you sorted out. If not, another possible cause of
this message is that Access is confused about the data types. To solve this
issue, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[Guest]

Dear Allen
Many thanks - but I tried what you suggested and it did not work
I have compressed the database after unchecking the boxes under:
Tools | Options | General | Name AutoCorrect
But no no avail.
I still get the "Query too complex" message.
Also, I found when I ran the query from design mode, it would work. But
after I saved it and ran it from the query, I go the "Query too complex
message" - BUT it just ran the query from design mode so it cannot be too
complex!!
Any other ideas - or do I have to pay $390 to Microsoft tech service to fix
the problem?

 

[Allen Browne]

Switch the query to SQL View (View menu, from query design).
Copy the SQL statement, and post it here.
It might give us some clues.