Subform filtering based off multiple parameters (Combobox AND Textbox)

Question

I want to filter a subform based on two parameters on a form (combobox and textbox).

I have a Form with combobox cboTimePeriod which show data from table TimePeriod (example TimePeriod=”10.01.2018-10.02.2018”; ID=12).

Combobox data:

Data Row source= SELECT [tblTimePeriod].[TimePeriod], [tblTimePeriod].[ID] FROM tblTimePeriod ORDER BY [TimePeriod];

Data Bound Column=2 (bound to ID)

Format Column Count=1 (shows textual value of TimePeriod) Format List Width=2,54cm

Also I have some cbobuttons with Cities, so when I press a button named "Boston", TextBox txtCity shows i.e. “Boston”.

What I want is when ever I select time period (cboTimePeriod), result in a subform has to be filtered based on these two parameters (selected TimePeriod AND City in textbox).

And as you might guessed, it does not work.

I tried several codes, here is the one I've used:

'How do I filter an Access subform with multiple combo boxes in the form?

And this is my implementation, which does not work:

Dim strWhere As String

 If Nz(Me.cboTimePeriod, "") <> "" Then
strWhere = strWhere & "[TimePeriodID] = '" & Trim(Me. cboTimePeriod) & " ' AND "
End If

If Nz(Me.txtSelectedCity, "") <> "" Then
    strWhere = strWhere & "[CityName] = '" & Trim(Me. txtSelectedCity) & " ' AND "
End If

If strWhere <> "" Then
    strWhere = Left(strWhere, Len(strWhere) - 5)
    Me.qry_SomeData_subform.Form.Filter = strWhere 'after this line, function exits the code        
Me.qry_ SomeData_subform.Form.FilterOn = True
Else    
    Me.qry_ SomeData_subform.Form.Filter = ""      
    Me.qry_ SomeData_subform.Form.FilterOn = False
End If

strWhere gives this: strWhere = "[TimePreriodID] = '12 ' AND [CityName] = 'Boston '"

After aplying a filter, on a subform "Unfiltered" changes to "Filtered", but with no change in data.

Any help is appreciated.

Gustav Gustav · Accepted Answer · 2018-02-16T12:17:08

I guess TimePeriod is numeric, thus (and correction the spaces):

strWhere = strWhere & "[TimePeriodID] = " & Me!cboTimePeriod.Value & " AND "

and:

strWhere = strWhere & "[CityName] = '" & Trim(Me!txtSelectedCity.Value) & "' AND "

Edit: To look up another column, here the second column:

strWhere = strWhere & "[CityName] = '" & Trim(Me!txtSelectedCity.Column(1)) & "' AND "

Subform filtering based off multiple parameters (Combobox AND Textbox)

1 Answers