Solved Filter continuous form on dates interval

[mishash]

frmListOfOrders (based on qryListOfOrders) has unbound StartDate and EndDate text boxes and search button btnSearch .

The search sub is:

Sub Search()
Dim strCriteria, task As String
    strCriteria = "OrderDate between " & Format(Me.StartDate, "\#mm\/dd\/yyyy\#") & " and " & Format(Me.EndDate, "\#mm\/dd\/yyyy\#")
    task = "select * from qryListOfOrders where (" & strCriteria & ")"
    DoCmd.ApplyFilter task
End Sub

The btnSearch code is:

Private Sub btnSearch_Click()
    Call Search
End Sub

I enter StartDate and EndDate (say, 09\01\2020 and 09\30\2020), click on the btnSearch - the result is records from 09\01\2020 to09\29\2020 (the last date of the interval omitted, as if it was coded <EndDate instead of <=EndDate. There are records dated 09\30\2020, though.

What do I miss?

 

[Gasman]

Time element in date field?

 

[mishash]

Time element in date field?

OrderDate field, Date/Time type, Medium Date format
I recall now I've read something about time element playing games, but don't remember exactly.

 

[Gasman]

I am asking does the field get populated with Date()or Now()?, format does not come into it.

Try 23:59 with the EndDate ?

 

[mishash]

I am asking does the field get populated with Date()or Now()?, format does not come into it.

Try 23:59 with the EndDate ?

The Default Value of OrderDate is =Now() indeed. The unbound StartDate and EndDate text boxes do not have default values.
I only enter date (select from the Date Picker)

 

[Gasman]

So #09/30/2020 17:15# is greater than #09/30/2020# ? and hence will not be included.?

See where I am going with this?

 

[mishash]

So #09/30/2020 17:15# is greater than #09/30/2020# ? and hence will not be included.?

See where I am going with this?

I guess I see.
Edited to

strCriteria = "Orderdate between " & Format(Me.StartDate, "\#mm\/dd\/yyyy\#") & " and " & Format(Me.EndDate + 1, "\#mm\/dd\/yyyy\#")

Thank you!

 

[Gasman]

I'd be careful with that format as I am sure the Between would pick up 10/01/2020?

Test it to see.

I'd use a format of

"\#mm\/dd\/yyyy\ 23:59#")

or

"\#mm\/dd\/yyyy\ 23:59:59#")

 

[mishash]

I am sure the Between would pick up 10/01/2020?

only the midnight between 09/30 and 10/01, no?

 

[Gasman]

Yes. If you know you data will never be dead on 00:00 for the next day, then you would be OK, but only you know your data.?
Remember this though, if you process shift work.

 

[mishash]

Yes. If you know you data will never be dead on 00:00 for the next day, then you would be OK, but only you know your data.?
Remember this though, if you process shift work.

Thanks again.
A side question.

Sub Search()

Dim strCriteria, task As String

    strCriteria = "OrderDate between " & Format(Me.StartDate, "\#mm\/dd\/yyyy\#") & " and " & Format(Me.EndDate, "\#mm\/dd\/yyyy\#")

    task = "select * from qryListOfOrders where (" & strCriteria & ")"

    DoCmd.ApplyFilter task

End Sub

The line task = "select * from qryListOfOrders where (" & strCriteria & ")"
I was sure it filters the underlying query. I've just noticed I could put any name instead of qryListOfOrders. like tblTest or 23, and the result would still be correct.
How come?

 

[Gasman]

As long as you concatenate the label/control value with the rest of the statement, much as you did for the criteria?

I would have just used the criteria as the filter property and then set filter on.

I have never used ApplyFilter. https://docs.microsoft.com/en-us/office/vba/api/access.docmd.applyfilter

In fact it appears all you need is the criteria?

Your task string would be more appropriate for setting the recordsource of the form?

 

[mishash]

As long as you concatenate the label/control value with the rest of the statement, much as you did for the criteria?

I would have just used the criteria as the filter property and then set filter on.

I have never used ApplyFilter. https://docs.microsoft.com/en-us/office/vba/api/access.docmd.applyfilter

In fact it appears all you need is the criteria?

Your task string would be more appropriate for setting the recordsource of the form?

All I need is criteria, correct.
I know how to apply a fixed criteria - in this form I have btnCurrentYear, btnLastMonth etc with predetermined filters, for example:

Private Sub btnCurrentMonth_Click()
    DoCmd.ApplyFilter , "MONTH(OrderDate)=MONTH(Date())"
End Sub

But I could not figure out how to apply this method with unbound StartDate and EndDate text boxes. Now I've fixed this too:

Private Sub btnSearch_Click()
DoCmd.ApplyFilter , "Orderdate between " & Format(Me.StartDate, "\#mm\/dd\/yyyy\#") & " and " & Format(Me.EndDate + 1, "\#mm\/dd\/yyyy\#")
End Sub