How increase date filter on one day (1 Viewer)

[eugzl]

Hi All.
I would like to filter different ListBoxes RowSource by value which differ by one day. For this I created a code

Private Sub cmdFilter_Click()
  Dim strFilter As String

  strFilter = "CompDate = #" & Format(Me.txtFilter.Value, "mm/dd/YYYY") & "#"
  Me.lbxMon.RowSource = "SELECT * FROM qDailyMON WHERE " & strFilter
  Me.lbxTue.RowSource = "SELECT * FROM qDailyTUE WHERE " & strFilter + 1
  Me.lbxWed.RowSource = "SELECT * FROM qDailyWED WHERE " & strFilter + 2
End Sub

But when I ran the code I got an error. How to fix Tuesday and Wednesday ListBox filtering problem by increasing the filter value by 1 and 2 respectively?

Thanks

 

[Gasman]

Use DateAdd() https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dateadd-function before formatting.
Might not even need to use Format() ?
Test and see.

 

[Josef P.]

[OT]

Might not even need to use Format() ?

Without Format it's a lottery depending on the windows language settings.
CStr unfortunately depends on the region settings.

Using the date format dd.mm.yyyy (typical german windows config) results in:

Dim SqlText As String

Debug.Print #3/13/2023# ' => 13.03.2023
SqlText = "select #3/2/2023# as Ok, #" & #3/13/2023# & "# as Wrong from TestTab"

With CurrentDb.OpenRecordset(SqlText) ' <-- Error 3075
         ' Syntax error in date in query expression '#13.03.2023'.
   .Close
End With

 

[eugzl]

Use DateAdd() https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dateadd-function before formatting.
Might not even need to use Format() ?
Test and see.

Hi Gasman. Thanks for reply.
I modify code like you suggested

Me.lbxMon.RowSource = "SELECT * FROM qDailyMON WHERE " & CopmDate = DateAdd("d", 1, Me.FilterDate)

After run the code I'm debuging. When I point cursor on Me.FilterDate it display date value that has filter TextBox. But CompDate, which is field of query, displays value Empty.
Where is a problem?

Thanks

 

[Gasman]

Try

Dim strSQL as string
strSQL= "SELECT * FROM qDailyMON WHERE  CopmDate = #"  & DateAdd("d", 1, Me.FilterDate) & "#"
debug.print strSQL
Me.lbxMon.RowSource = strSQL

else add the Format back in as before?

Post back what strSQL shows from the Debug.Print. Date should either be in mm/dd/yyyy or yyyy-mm-dd format ?

 

[ebs17]

lbxMon, lbxTue, lbxWed sounds like listboxes for Monday, Tuesday, Wednesday.
You can filter on days of the week:

Me.lbxMon.RowSource = "SELECT * FROM qDailyMON WHERE Weekday(CompDate, 2) = 1"
Me.lbxTue.RowSource = "SELECT * FROM qDailyMON WHERE Weekday(CompDate, 2) = 2"
' ...

Each new week has the same days of the week again. So if you want to display only one day per listbox, you would filter qDailyMON to a period of one week before.

Me.lbxMon.RowSource = "SELECT * FROM qDailyMON" & _
    " WHERE CompDate BETWEEN " & Format(Me.FilterDate, "#mm/dd/yyyy#")  & _
    " AND " & Format(Me.FilterDate + 6, "#mm/dd/yyyy#") & _
    " AND Weekday(CompDate, 2) = 1"

' ... and so on

In order to act a bit more skillfully there, one would have to know what takes place inside xxx in order to possibly adjust something there (I always have a bit of a hard time taking an unknown query for granted).
For more comprehensive tasks with larger amounts of data, one would very quickly resort to support via a well-planned calendar table.