I was having a similar problem recently.
In my case, it turned out that the query I was trying to use as the source for my UPDATE statement had a Count or Sum statement in it.
For some bizarre reason, I think Access cannot use an Aggregate query (one with Count or Sum or Average etc) as the source for an UPDATE query. Beats me why.
Anyway, my solution was to SELECT myQuery INTO NewTable then as a separate query I did an UPDATE to transfer results from NewTable to TargetTable.
Obviously this takes considerably longer (well, about twice as long) but is probably bearable if your DB isn't too big. Also, it kind of requires that you're making your queries in VBA so that you can delete your temp tables when you've finished with them.
Anyway, it's the only solution I have managed to come up with.