MoNoGoAt MoNoGoAt - 6 months ago 44
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

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
Comments