MoNoGoAt MoNoGoAt - 1 year ago 154
Vb.net Question

Filter a Dataset with Multiple Conditions

I need to filter a large amount of data from an access database. I am building a program to accomplish this, but am running into an issue for using multiple criteria in the filter. For instance, here is test code for selection using a given month and color:

If RadioButton1.Checked Then
If RadioButton4.Checked Then
Me.BindingSource.Filter = "[January] = 'True' AND [Red] = 'True'"
ElseIf RadioButton5.Checked Then
Me.BindingSource.Filter = "[January] = 'True' AND [Yellow] = 'True'"
ElseIf RadioButton6.Checked Then
Me.BindingSource.Filter = "[January] = 'True' AND [Blue] = 'True'"
Else Me.BindingSource.Filter = "[January] = 'True'"
End If
ElseIf RadioButton2.Checked Then
If RadioButton4.Checked Then
Me.BindingSource.Filter = "[February] = 'True' AND [Red] = 'True'"
ElseIf RadioButton5.Checked Then
Me.BindingSource.Filter = "[February] = 'True' AND [Yellow] = 'True'"
ElseIf RadioButton6.Checked Then
Me.BindingSource.Filter = "[February] = 'True' AND [Blue] = 'True'"
Else Me.BindingSource.Filter = "[February] = 'True'"
End If
ElseIf RadioButton3.Checked Then
If RadioButton4.Checked Then
Me.BindingSource.Filter = "[March] = 'True' AND [Red] = 'True'"
ElseIf RadioButton5.Checked Then
Me.BindingSource.Filter = "[March] = 'True' AND [Yellow] = 'True'"
ElseIf RadioButton6.Checked Then
Me.BindingSource.Filter = "[March] = 'True' AND [Blue] = 'True'"
Else Me.BindingSource.Filter = "[March] = 'True'"
End If
End If


Now this is a large amount of code for even three months and colors. There has to be a better way to do this, right? With as many fields in the table I need to sort it would take me years to do it this way.

Answer Source

You need to split out the month buttons from the colour buttons, along the lines of:

Dim wc as string
'months
    if RadioButto1.Checked then wc = "January = true"
elseif radiobutton2.checked then wc = "February = true"
. . . . 

end if
'colours
If radiobutton4.checked then
wc &= " AND Red = true"
elseif radiobutton5.checked then
wc &= " AND Yellow = true"
. . . . .
end if


me.bindingsource.filter = wc