Query returns more records standalone than in VBA

Question

I've got a table in sql server with a date column formatted as datetime. The date is added to this table either manually or thru an automated import process. If manually, just the date gets written to the sql table. If automated, the date and time get written. For example, if manually added, 2021-09-29 00:00:00.000 gets written to the database. Otherwise 2021-09-29 15:23:38.000 would be written.

In Access, this data is displayed as such:

If I run this query in the query designer, I get the rows I expect:
SELECT tblOrder.OrderID, tblOrder.ReceivedDate
FROM tblOrder
WHERE (((tblOrder.ReceivedDate)>=#9/29/2021# And (tblOrder.ReceivedDate)<=#9/29/2021 23:59:59#))
ORDER BY tblOrder.ReceivedDate DESC;

If I code this query in a VBA module (which is in the Report_Open module in an Access report) only those rows with 9/29/2021 00:00:000 are returned. The rest is ignored.

So, I modified the query in VBA like so:

SELECT tblOrder.OrderID, tblOrder.ReceivedDate  
FROM tblOrder  
WHERE (((FormatDateTime(tblOrder.ReceivedDate, 0)>=#9/29/2021# And (FormatDateTime(tblOrder.ReceivedDate, 0))<=#9/29/2021 23:59:59#))  
ORDER BY tblOrder.ReceivedDate DESC;  

But it still ignores the rows with the format mm/dd/yyyy hh:mm:ss.

The query returns the desired rows only when run in the query designer.

How do I get the query in VBA to match the query in the designer?

Thanks.

Answer 1

Yes, I get the correct records if I run that in query designer.

Yeah, I'm going to try updating table data with missing time. Or I may just redevelop the report in something other than Access.

Thanks for your help.

Answer 2

Yeah, maybe like Crystal Reports. Good luck!