Basic filter question... filter with like

[Ruegen]

Trying to filter with like



I have

Me.Filter = "[Area] Like" & "*" & Me.Text36 & "*"
Me.FilterOn = True

however I get an error and I am not sure as to why...

[June7]

Is Area a text field?

LIKE is a text comparison operator. Need text delimiters:

Me.Filter = "[Area] Like '*" & Me.Text36 & "*'"

[Ruegen]

Is Area a text field?

LIKE is a text comparison operator. Need text delimiters:

Me.Filter = "[Area] Like '*" & Me.Text36 & "*'"

thanks - noted

[Ruegen]

Working on it now and the filter works however

I have two controls, one is a textbox and one is a drop down

I want to filter correctly using either of those two or in combination

so area is filtered by the text box and organiser is filtered by the combo box

I tried

Me.Filter = "[Area] Like '*" & Me.txtAreaFilter & "*'" & " and TourOrganiserID =" & Nz(Me.cmbTourOrganiser, [TourOrganiserID])
Me.FilterOn = True

however I get mixed results

instead of showing all the results if I type say an area "NSW" I show only one of the organizers instead of all the organizers.

The same action would happen on an after update event with the combo box

[Ruegen]

I am using

If IsNull(Me.cmbNameFilter) Then
Me.Filter = "[Area] Like '*" & Me.txtAreaFilter & "*'"
Me.FilterOn = True
Else
Me.Filter = "[Area] Like '*" & Me.txtAreaFilter & "*'" & " and TourOrganiserID =" & Me.cmbNameFilter
Me.FilterOn = True
End If

to get around this but I would like to know if there is a better way

[June7]

Not really - but this is an option:
Me.Filter = "[Area] Like '*" & Me.txtAreaFilter & "*'" & IIf(Not IsNull(Me.cmbNameFilter), " and TourOrganiserID=" & Me.cmbNameFilter, "")

[Ruegen]

Not really - but this is an option:
Me.Filter = "[Area] Like '*" & Me.txtAreaFilter & "*'" & IIf(Not IsNull(Me.cmbNameFilter), " and TourOrganiserID=" & Me.cmbNameFilter, "")

It all works ok - even the button to filter non nulls to show only the nulls in a particular field - however on the afterupdate of the combo boxes it filters to null when I only want to to filter if the button has a certain caption

I am using

Me.Filter = "[Area] Like '*" & Me.Text36 & "*'" & IIf(Not IsNull(Me.Text38), " and SchoolTypeID=" & Me.Text38, "") & IIf(Not IsNull(Me.txtStates), " and StateID=" & Me.txtStates, "") & IIf(Me.Command47.Caption = "Blank Emails", "and SchoolEmail is Null", "")
Me.FilterOn = True

The problem occurs when you change SchoolType it filters the form to show null emails even when the button caption is on "Show All"

[Ruegen]

It all works ok - even the button to filter non nulls to show only the nulls in a particular field - however on the afterupdate of the combo boxes it filters to null when I only want to to filter if the button has a certain caption

I am using

Me.Filter = "[Area] Like '*" & Me.Text36 & "*'" & IIf(Not IsNull(Me.Text38), " and SchoolTypeID=" & Me.Text38, "") & IIf(Not IsNull(Me.txtStates), " and StateID=" & Me.txtStates, "") & IIf(Me.Command47.Caption = "Blank Emails", "and SchoolEmail is Null", "")
Me.FilterOn = True

The problem occurs when you change SchoolType it filters the form to show null emails even when the button caption is on "Show All"

nevermind I swapped Blank Emails with Show All - my dufus brain.

[June7]

You might find this interesting. The following criteria for a number field in a query works:

LIKE "*" & [input value] & "*"

I tested on a number field with year values (2012, 2013, 2014). I entered 14 as the parameter and all the 2014 records returned.

[Ruegen]

You might find this interesting. The following criteria for a number field in a query works:

LIKE "*" & [input value] & "*"

I tested on a number field with year values (2012, 2013, 2014). I entered 14 as the parameter and all the 2014 records returned.

cheers thanks

I'm re-designing a few forms to VBA filters rather than record source criteria that lead to forms although I don't know if there is an improvement to speed (if I am not having to requery then maybe? - but I assume filterOn = true requeries anyway)