OK, got to the bottom of it. [Phew:]
In the form you have a default value for date (stupid name btw, as it is a reserved name) as ='01/01/2022'
If you click the calendar icon on the form, it opens to that date. Even when you put 01/04/2023 in the form, the value still comes up as 01/01/2022. Tested that in the immediate window and then removed the default and tested again.
Once that default was removed, the query behaves as it should. Well in fact it has always been behaving as it should, it was just getting the incorrect data to work with.