ComboBox to select filter criteria

[PC User]

I'm building a filter using comboboxes and I'm having trouble with two of them. One is to filter by records that are completed or by records that are not completed. The other combobox is to filter by records that have comments or by records that have no comments.

Filter7
Row Source Type: Value List
Row Source: Completed;[tblMainData.Status]='Complete';Not Completed;[tblMainData.Status]<>'Complete'
Column Count: 2
Bound Column: 1

Filter8
Row Source Type: Value List
Row Source: Comments;[tblMainData.Comments] Is Not Null;No Comments;[tblMainData.Comments] Is Null
Column Count: 2
Bound Column: 1

The VBA Code is:
==================
If Me(Filter7) <> "" Then
strWhere = strWhere & " " & Me.Filter7.Column(1) & " " & " And "
Debug.Print strWhere
End If
If Me(Filter8) <> "" Then
strWhere = strWhere & Me.Filter8.Column(1) & " And "
Debug.Print strWhere
End If
==================

I'm getting an error on building the strWhere variable. Can I use filter criteria statements in a combobox column?

Thanks,
PC

 

[SammyJ]

Maybe use Me.Filter7.Value <> "" in your if statement?

Hard to help as you don't say what or where the error is?

Take the square brackets out of your RowSouce as well, or maybe just reduce it to "Is Null" or "Is Not Null" and bring the field name back to VBA. Or just have a one-column combo and check the value in VBA. The more I can do in VBA the better I always think. Easier to maintain.

The square brackets are incorrect. They either need to be gone entirely, or around both the table and field name. Take em out I say.

Hope some of this helps.

Sam.