Bizarre and frustrating! Phantom ambiguous field reference in a query

  • Thread starter Thread starter Bill Reed via AccessMonster.com
  • Start date Start date
B

Bill Reed via AccessMonster.com

Just when you think you've seen everything.

I have a query that's been working ok as the datasource for a form. Now I
want to filter that query using a "Where" clause string that's
concatenated when the db opens. I added a query to the form's datasource
query to provide the necessary fields for the "Where" clause to filter on,
and now a field, "Status", that heretofore never gave me a problem, is
displaying as "Status.Status" in the query result field heading, as though
there were another table/query in the grid that had the same field name. I
can absolutely assure you that is not the case. The only instance of that
field name is in the table "Status". (I'm stuck with the names of these
tables which were created by a novice without regard to possible
duplication of object names (or, in this case, field names identical to
parent table names)). But I am completely baffled as to why the original
query, which also only had one field named "Status" in the grid, had no
difficulty with the field name being the same as the parent table name.

This nonsense has repercussions throughout the db.

Has anyone seen anything like this before?

Thanks
 
If this is Access 2000 or later, the first thing to check is that you have
unchecked the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

After that, compact the database.
You could also switch the query to SQL View, copy the (correct) query
statement to clipboard, delete the query, create a new query, switch it to
SQL View, and paste in the SQL statement. That should completely stop Access
from confusing the objects based on the Name AutoCorrect info.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
Thanks for the advice. I had already cleared the autocorrect nonsense. So
that wasn't the cause of the wierdness.

I ended up redesigning the query from the beginning. I guess this is just
one of those mysteries that is destined to remain a mystery, at least until
the next time something like it happens ;-)!!
 
Back
Top