question

EM-0355 avatar image
0 Votes"
EM-0355 asked thedbguy answered

Query returns more records standalone than in VBA

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:
136339-capture.png




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.

office-access-dev
capture.png (7.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BobLarson-6601 avatar image
0 Votes"
BobLarson-6601 answered

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;

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EM-0355 avatar image
0 Votes"
EM-0355 answered BobLarson-6601 commented

Thanks for your replies.

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

@BobLarson-6601 , 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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

My suggestion should have worked because it pulls everything from the first date and the date add would mean it would pull all that are less than the next day. So, something else is going on there. So post what you actually used for VBA from my suggestion.

The VBA is the same -

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

And don't get hung up on adding the parentheses. They aren't needed and can only cause you problems.

0 Votes 0 ·
thedbguy avatar image
0 Votes"
thedbguy answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EM-0355 avatar image
0 Votes"
EM-0355 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Yes, I would also recommend changing the variable to date; but also, I would add an explicit conversion to dates when assigning the values. For example:

 Dim begDateWork As Date
 Dim endDateWork As Date
    
 begDateWork = CDate(Forms!frmPrintReportDialog.BegDate)
 endDateWork = begDateWork + 1

Then, I would also make sure to format the SQL dates to comply with Access requirements. For example:

 "WHERE " &...
 ...
 tblOrder.ReceivedDate >=#" & Format(begDateWork, "yyyy-mm-dd") & "# AND tblOrder.ReceivedDate < #" & Format(endDateWork, "yyyy-mm-dd") & "#"


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EM-0355 avatar image
0 Votes"
EM-0355 answered

@thedbguy I made your changes but get the same result:

 Private Sub Report_Open(Cancel As Integer)
    
     Dim strSQL As String
     Dim begDateWork As Date
     Dim endDateWork As Date
        
     begDateWork = CDate(Forms!frmPrintReportDialog!BegDate)
     endDateWork = begDateWork + 1
    
     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 >= #" & Format(begDateWork, "yyyy-mm-dd") & "# AND " tblOrder.ReceivedDate < #" & Format(endDateWork, "yyyy-mm-dd") & "#)"
    
     Me.RecordSource = strSQL
    
 End Sub
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Sorry to hear that. One last chance. Add Debug.Print strSQL and post the result here.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EM-0355 avatar image
0 Votes"
EM-0355 answered
 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 >= #2021-09-29# AND tblOrder.ReceivedDate < #2021-09-30#) 
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Thanks! That looks fine to me. Just to be clear, if you copy and paste that into the Query Designer and run it, you get the correct records, right? If so, I'm not sure what else to suggest other than perhaps update your table's data to add missing time components. Good luck!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.