Microsoft Access is confused

The other day while working on a legacy Microsoft Access database I was reminded of how bizarre Access can be. When I ran the following query:

SELECT * FROM ([Authors]
INNER JOIN [Publications]
ON [Authors].id = Str([Publications].[authors].VALUE))
WHERE ( [Publications].[date] BETWEEN [Begin Date] AND [End Date] )

I received a peculiar error:

Microsoft Access The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement.

For a query with no outer joins, this was terribly unhelpful.

Researching the error, I found solutions like, "change all of the joins to INNER" which obviously didn't help me. So I began trying solutions that didn't make sense. Ultimately I found a version that worked:

SELECT * FROM ([Publications]
LEFT JOIN [Authors]
ON [Authors].id = Str([Publications].[authors].VALUE))
WHERE ( [Publications].[date] BETWEEN [Begin Date] AND [End Date] )

By reversing the order of the tables and changing the INNER join to a LEFT join, the query works. Neither change solved the problem alone.

So next time you're faced with a confusing Microsoft Access error message, try doing the opposite!