CODEWITHSUNDEEP
sqlms-access
James Parsons
James Parsons

Reputation: 6057

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?

Upvotes: 0

Views: 1557

Answers (2)

Gustav
Gustav

Reputation: 55961

Access doesn't know what data type your parameters are, so specify that in the query:

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

Upvotes: 1

June7
June7

Reputation: 21379

Parameters worked to filter a native field - not the calculated date. However, using CDate() function worked.

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

But before I remembered that, I tested calculating with inputs back to a birthdate range which also worked.

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

Upvotes: 0

Related Questions