Solved Filter continuous form on dates interval (1 Viewer)

mishash

Member
Local time
Today, 12:58
Joined
Aug 20, 2020
Messages
52
frmListOfOrders (based on qryListOfOrders) has unbound StartDate and EndDate text boxes and search button btnSearch .

The search sub is:
Code:
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:
Code:
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?
 

mishash

Member
Local time
Today, 12:58
Joined
Aug 20, 2020
Messages
52
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

Enthusiastic Amateur
Local time
Today, 10:58
Joined
Sep 21, 2011
Messages
13,965
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

Member
Local time
Today, 12:58
Joined
Aug 20, 2020
Messages
52
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

Enthusiastic Amateur
Local time
Today, 10:58
Joined
Sep 21, 2011
Messages
13,965
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

Member
Local time
Today, 12:58
Joined
Aug 20, 2020
Messages
52
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
Code:
strCriteria = "Orderdate between " & Format(Me.StartDate, "\#mm\/dd\/yyyy\#") & " and " & Format(Me.EndDate + 1, "\#mm\/dd\/yyyy\#")
Thank you!
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:58
Joined
Sep 21, 2011
Messages
13,965
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
Code:
"\#mm\/dd\/yyyy\ 23:59#")
or
Code:
"\#mm\/dd\/yyyy\ 23:59:59#")
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 10:58
Joined
Sep 21, 2011
Messages
13,965
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

Member
Local time
Today, 12:58
Joined
Aug 20, 2020
Messages
52
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.
Code:
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

Enthusiastic Amateur
Local time
Today, 10:58
Joined
Sep 21, 2011
Messages
13,965
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

Member
Local time
Today, 12:58
Joined
Aug 20, 2020
Messages
52
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:

Code:
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:
Code:
Private Sub btnSearch_Click()
DoCmd.ApplyFilter , "Orderdate between " & Format(Me.StartDate, "\#mm\/dd\/yyyy\#") & " and " & Format(Me.EndDate + 1, "\#mm\/dd\/yyyy\#")
End Sub
 
Last edited:

Users who are viewing this thread

Top Bottom