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

What happens if you don't use the FormatDateTime() function?

Answer 2

You should be able to get what you want by using this:

SELECT tblOrder.OrderID, tblOrder.ReceivedDate
FROM tblOrder
WHERE tblOrder.ReceivedDate >= #9/29/2021# And < DateAdd("d",1, tblOrder.ReceivedDate)
ORDER BY tblOrder.ReceivedDate DESC;

Answer 3

Thanks for your replies.

@DBG , the FormatDateTime function was not used originally. That's what prompted me to try the function.

@Bob Larson (Access MVP 2008, 2009, 2011) , I tried your suggestion but get the same results. Within VBA, only the rows formatted mm/dd/yyyy are returned. Within the designer, all rows are returned.

I'm working with Access 2007. Maybe there's something internal within 2007 reporting that is causing this.

Answer 4

Hi. Would you mind posting the VBA code instead? Thanks.

Answer 5

VBA code below.

 (I noticed that begDateWork and endDateWork were both declared as string.  I changed them to date but that didn't fix the issue.
  Both Forms!frmPrintReportDialog!BegDate and EndDate are formatted mm/dd/yy)

---

Private Sub Report_Open(Cancel As Integer)

    Dim strSQL As String
    Dim begDateWork As String
    Dim endDateWork As String

    begDateWork = Forms!frmPrintReportDialog!BegDate
    endDateWork = DateAdd("d", 1, Forms!frmPrintReportDialog!BegDate)

    strSQL = _
        "SELECT " & _
            "tblOrder.*, " & _
            "tblDealer.CoName, " & _
            "IIf([TotalPrice]>0,[TotalPrice],[EstTotalPrice]) AS TPrice, " & _
            "IIf(IsNull([ShipDate]),[EstScheduleShipDate],[ShipDate]) AS NeedsDate, " & _
            "IIf([TotalCubes]>0,[TotalCubes],[EstTotalCubes]) AS EstCubesTotal, " & _
            "IIf(IsNull([ShipDate]),'Est','') AS EstNeeds, " & _
            "IIf([TotalPrice]>0,'','Est') AS EstPrice, " & _
            "IIf([TotalCubes]>0,'','Est') AS EstCubes " & _
        "FROM tblOrder " & _
            "INNER JOIN tblDealer ON tblOrder.CustID = tblDealer.CustID " & _
        "WHERE " & _
            "(((tblOrder.OrderType) <> 'ESO') AND " & _
            "((tblOrder.Closed) = No)) AND " & _
            "(tblOrder.ReceivedDate >= #" & begDateWork & "# AND tblOrder.ReceivedDate < #" & endDateWork & "#)"

    Me.RecordSource = strSQL

End Sub