I have a combobox where the rowsource is a dynamically constructed SQL Select statement.
This selection includes a comparison between a date field from the form and a date field from the query from which the SQL statement selects rows.
This date comparison is not working properly.

This is my dynamic SQL select statement:-
"SELECT DISTINCT fieldA, fieldB FROM QueryName WHERE EffectiveFromDate <= #" & CourseStartDate & "# AND PrincipalTrainer ORDER BY fieldB;"

Where
- EffectiveFromDate is a date field in the Form
- CourseStartDate is a date field from the database query I have called QueryName
- PrincipalTrainer is a boolean true/false field also from the database query

The selection in my code does not work for the case where the dates are in the same year:  
so when EffectiveFromDate = 01/05/2022 (dd/mm/yyyy format) and CourseStartDate = 03/10/2022, the record is not selected.

But when the EffectiveFromDate is in a previous year, it does work.
so when EffectiveFromDate = 01/05/2021 (dd/mm/yyyy format) and CourseStartDate = 03/10/2022, the record is selected.

Can you please let me know what is wrong with my code?

Please note that this is on a UK PC where the date format in Windows is dd/mm/yyyy.