Access query with date BETWEEN breaks when using parameters

I have an SQL query in access that will grab all records where a calculated date is in between two values. It works fine if I hardcode date literals such as:

SELECT *  FROM Table  WHERE DateAdd("d",-60,DateAdd("yyyy",65,[Table].[BirthDate])) Between #3/21/2021# And #3/27/2021#;  

However I need to parametrize the the between dates so that they can be entered by a user like:

SELECT *  FROM Table  WHERE DateAdd("d",-60,DateAdd("yyyy",65,[Table].[BirthDate])) Between [StartDate] And [EndDate];  

However when I run the latter query and enter the exact same dates as the former, hard-coded one, it starts pulling records outside the between range. I've attempted to enter the dates like 3/21/2021 as well as date literals like #3/21/2021# and neither work. The latter doesn't pull anything at all.

I also have a form with a handful of text boxes using the short date format that let the user pick the dates for the query. It has the same issue of pulling back incorrect records. None of the records have any time component to my knowledge.

How can I get the date between to correctly work with user entered parameters?

https://stackoverflow.com/questions/66847133/access-query-with-date-between-breaks-when-using-parameters March 29, 2021 at 07:30AM