I have a query which spans several tables, and successfully extracts all required data exactly how it should. The database is a match score archive and statistics system for a table tennis league, and contains tables for Home Teams; Away Teams; Fixture Details; Start Times; Home Nights (the night of the week the teams play at home) and Home Venues.
This all works correctly in the query, which looks up all stored data about all fixtures. However, when I place this data into a form, the two Team Name fields (AwayTeamName and HomeTeamName) are missing, and have #Name? instead. All of the other fields work correctly, so this is quite a mystery to me. I tried changing the control source to the records in the tables, and for some reason this gave the Team Name for both teams. (The fields are both called TeamName, but are in different tables). I tried chaning to control source to the query, but this only gave me #Name?. Adding Square brackets into the expression gave me #Error instead, but no useful values.
BUT, I changed the join property between tblTeams and tblFixtures in the query to type 3 instead of type 1, and it worked successfully - this sounds like a bug to me. Has anyone else experienced this, or does anyone know why it happens?