I am looking for some help from this community.
I copy the following code to filter a list box and various combo boxes. I am having a hart time adding more code to the STRWhere statement. When I select items from the list box and also select an item from the combo box, the information is working fine. the problem is when nothing is selected on the combo box the report bring a mismatch. I tried the null value and the zero and nothing is working.
I just need help on adding more filtering data to the STRWhere statement.
It will be like five options to select from
I copy the following code to filter a list box and various combo boxes. I am having a hart time adding more code to the STRWhere statement. When I select items from the list box and also select an item from the combo box, the information is working fine. the problem is when nothing is selected on the combo box the report bring a mismatch. I tried the null value and the zero and nothing is working.
I just need help on adding more filtering data to the STRWhere statement.
It will be like five options to select from
Code:
Private Sub brnFilter_Click()
On Error GoTo ProblemHandle
Dim DB As Database
Dim RS As Recordset
Dim I As Integer
Dim StrSQL As String
Dim VarianteValue As Variant
Dim StrWhere As String
Dim StrIn As String
Dim FlagSelectAll As Boolean
Dim BEntity As Long
Dim BCoCode As Long
Dim STREntity As String
BEntity = Nz(Me.cboentity)
Set DB = CurrentDb
Set RS = DB.OpenRecordset("SELECT * FROM tbbilling")
For I = 0 To Me.ListBox.ListCount - 1
If Me.ListBox.Selected(I) Then
If Me.ListBox.Column(0, I) = "ALL" Then
FlagSelectAll = True
End If
StrIn = StrIn & "" & Me.ListBox.Column(0, I) & ","
End If
'
Next I
StrWhere = " WHERE [tbbilling.benefitsID] in " & _
"(" & Left(StrIn, Len(StrIn) - 1) & ")" _
& " AND [tbbilling.entityid] = " & BEntity & ""
StrSQL = "INSERT INTO tbbillingReport SELECT tbbilling.* FROM tbbilling "
StrSQL = StrSQL & StrWhere
Debug.Print StrSQL
End If
DoCmd.RunSQL StrSQL
For Each VarianteValue In Me.ListBox.ItemsSelected
Me.ListBox.Selected(VarianteValue) = False
Next VarianteValue
Exit Sub
DB.Close
Set DB = Nothing
RS.Close
End Sub
Last edited: