Query returns more records standalone than in VBA

EM 271 Reputation points

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?


Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
798 questions
0 comments No comments
{count} votes

12 answers

Sort by: Most helpful
  1. DBG 2,306 Reputation points

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

    0 comments No comments

  2. 2021-09-29T21:17:27.29+00:00

    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;

    0 comments No comments

  3. EM 271 Reputation points

    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.

  4. DBG 2,306 Reputation points

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

    0 comments No comments

  5. EM 271 Reputation points

    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
    0 comments No comments