If you would post your schema and some sample data that would help. Without seeing your database, which i don't have access to, i am making some likely guesses as to the root cause.
But to clarify i'll share an example you can run in sql server (i don't have access sorry).
This example is more to show the issue of time stamps in your sql query.
DECLARE @Dates TABLE
(
Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DateExample DATETIME NULL
);
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 20:30:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 19:00:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 18:30:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 16:00:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 15:00:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 14:30:32.843')
SELECT * FROM @Dates WHERE DateExample BETWEEN '2016-09-08 00:00:00.000' AND '2016-09-08 00:00:00.000'
SELECT * FROM @Dates WHERE CAST(DateExample AS DATE) BETWEEN '2016-09-08 00:00:00.000' AND '2016-09-08 00:00:00.000'
The first select statement takes into account time stamps, and since your are stripping your times off of your dates being passed in , your comparing a date at 12:00am to other dates with a time stamp which is likely the cause of you not having any data returned.
The other issue that might be arising is you are converting a date to a string and that may not jive well with access.
The second query converts the dateexample column to date which makes the timestamp 12:00am and works with that query.
Given that T-SQL doesn't convert well to MS Access you have 2 options
1) Make sure your dates in your BETWEEN clause include time stamps that meet the criteria of what you are looking for. You may need to append 00:00:00 and 24:59:59.59 to your start and end dates of your BETWEEN statement in order to get all valid data.
2) I think MS Access may have functions that convert strings to date and in turn strip the time stamp off of it. I don't have MS access installed so i can't test this but I think this is possible from a quick google search.
ms access CAST - Google Search[
^]
Again, i don't have access to your DB so this may be off. If it is then feel free to provide schema and clarify your issue.