@AngelStrumpet.
Your current situation is causing you to include multiple problems into the same question. That isn't allowed (assuming you even knew you were doing it of course), but more importantly it won't help clarify matters for you to throw everything in together.
I suggest you take the SQL aside and test it separately, and not as part of a more complex structure whose details we're not privvy to and which we don't want to care about while dealing with the current issue. How it may fit into your more-complex query is a question for a separate thread when, and only when, this primary issue is resolved and clearly understood. So, let's work with the subquery as a stand-alone item in its own right for now. I suspect when brought into line with my earlier suggestions you'll find it works perfectly.
AngelStrumpet:
Since dates are pretty squirrely on Access I changed the field to "short text".
That would be highly non-recommended. Dates are not remotely
squirrely. They are perfectly logical and can work well for you if you use them correctly. That was the purpose of the link I included in my post #2. If you read and fully understand the points contained within that article you will find Dates both reliable and powerful tools to work with.
Having said that, converting dates to strings, or even testing them against strings without even first converting them, can be highly unreliable and should be avoided where at all possible.