filter subform in datasheet view on dates equal or after today

[megatronixs]

Hi all,

I try to build a filter via vba to filter a subform in datasheet view to show all dates after today's date.

The below is simply not working and after a lot of trying around still nothing. Any clue where I go wrong?

Private Sub btn_future_events_Click()
Dim Filter As String
Dim DateToday As Date
DateToday = ">= Date"
Filter = "[training_date_start] = DateToday"
Me!frm_create_event_subform.Form.Filter = Filter
Me!frm_create_event_subform.Form.FilterOn = True
End Sub

Greetings.

 

[June7]

Don't include variables within quote marks, they must be concatenated. Filter is a reserved word. Should not use reserved words as names for anything, including variables. Better would be strFilter. Or in the simple procedure you posted, don't even need a variable. Should name subform container something different from the form it holds, like ctrEvent.

Private Sub btn_future_events_Click()
Me.ctrEvent.Form.FilterOn = False
Me.ctrEvent.Form.Filter = "[training_date_start] >= Date()"
Me.ctrEvent.Form.FilterOn = True
End Sub

 

[megatronixs]

Hi June7,

I made it work like this:

Private Sub btn_future_events_Click()
Me!frm_create_event_subform.Form.Filter = "[training_date_start] >= Date()"
Me!frm_create_event_subform.Form.FilterOn = True
End Sub

Would it be possible to add also a combo box to the form and filter on months? as example using May as a number: 05

Greetings.

 

[megatronixs]

Hi,

I tried it with:

Private Sub Combo188_Click()
Dim DatePartNr As String
DatePartNr = Combo188.Value
Me!frm_create_event_subform.Form.Filter = "DatePart('m', [training_date_start] = DatePartNr)"
Me!frm_create_event_subform.Form.Filter = "DatePart('m', [training_date_start] = 5)"
Me!frm_create_event_subform.Form.FilterOn = True
End Sub

even when hard coding the May in the code still nothing.
It should filter all dates that have May in the date.

Greetings.

 

[June7]

The expression with hard-coded parameter has misplaced paren.

"DatePart('m', [training_date_start]) = 5"

And as for the variable version - remember, concatenate variables.

If the combobox value will be month number:

... = "Month([training_date_start]) = " & Me.combobox

If the combobox value will be month abbreviation:

... = "Format([training_date_start], 'mmm') = '" & Me.combobox & "'"

 

[megatronixs]

oops, silly me :-(
it works now thanks a lot.
this is the final version:

Private Sub Combo188_Click()
Me!frm_create_event_subform.Form.Filter = "Month([training_date_start]) = " & Me.Combo188
Me!frm_create_event_subform.Form.FilterOn = True
End Sub

and to clear the filters:

Private Sub btn_all_events_Click()
Me!frm_create_event_subform.Form.FilterOn = False
Me.Combo188 = ""
End Sub

Greetings.

 

[June7]

As in my example in post2, I would turn off the filter before setting the filter, in case user neglects to do that, which they will and then get upset when they don't get expected results.

 

[megatronixs]

OK, done