I have a form that has 5 listboxes on it. These list boxes are set to allow multiple selections (extended not simple) which the results show in the subform. My problem is that only one of my listboxes is working properly. The others keep saying that there is data mismatch. This because it is comparing a text to number field and I can't figure out how to get it to look at number to number. Here is my code.
Code:
Private Function BuildFilter() As Variant
Dim varWhere As Variant
BuildFilter = varWhere
Dim db As Dao.Database
Dim qdf As Dao.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strCriteria1 As String
Dim strCriteria2 As String
Dim strCriteria3 As String
Dim strCriteria4 As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("SearchEmail")
If Me!BusinessTypeList.ItemsSelected.Count > 0 Then
For Each varItem In Me!BusinessTypeList.ItemsSelected
strCriteria = strCriteria & "[Account List].BusinessType = " & Chr(34) & Me!BusinessTypeList.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "[Account List].BusinessType Like '*'"
End If
If Me!AccountTypeList.ItemsSelected.Count > 0 Then
For Each varItem In Me!AccountTypeList.ItemsSelected
strCriteria1 = strCriteria1 & "[Account List].AccountType = " & Chr(34) & Me!AccountTypeList.ItemData(varItem) & Chr(34) & "Or "
Next varItem
strCriteria1 = Left(strCriteria1, Len(strCriteria1) - 3)
Else
strCriteria1 = "[Account List].AccountType Like '*'"
End If
If Me!ProductTypeList.ItemsSelected.Count > 0 Then
For Each varItem In Me!ProductTypeList.ItemsSelected
strCriteria2 = strCriteria2 & "[Account List].[Product Type] = " & Chr(34) & Me!ProductTypeList.ItemData(varItem) & Chr(34) & "Or "
Next varItem
strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 3)
Else
strCriteria2 = "[Account List].[Product Type] Like '*'"
End If
If Me!RepList.ItemsSelected.Count > 0 Then
For Each varItem In Me!RepList.ItemsSelected
strCriteria3 = strCriteria3 & "[Account List].Rep = " & Chr(34) & Me!RepList.ItemData(varItem) & Chr(34) & "Or "
Next varItem
strCriteria3 = Left(strCriteria3, Len(strCriteria3) - 3)
Else
strCriteria3 = "[Account List].Rep Like '*'"
End If
If Me!CompanyNameList.ItemsSelected.Count > 0 Then
For Each varItem In Me!CompanyNameList.ItemsSelected
strCriteria4 = strCriteria4 & "[Account List].[Company Name] = " & Chr(34) & Me!CompanyNameList.ItemData(varItem) & Chr(34) & "Or "
Next varItem
strCriteria4 = Left(strCriteria4, Len(strCriteria4) - 3)
Else
strCriteria4 = "[Account List].[Company Name] Like '*'"
End If
strSQL = "SELECT * FROM [Account List] " & "WHERE " & "(" & strCriteria & ") AND (" & strCriteria1 & ") AND (" & strCriteria2 & ") AND (" & strCriteria3 & ") AND (" & strCriteria4 & ")" & "; "
Debug.Print strSQL
qdf.SQL = strSQL
Set db = Nothing
Set qdf = Nothing
End Function
Code:
Private Sub FilterSelections_Click()
'Update the record source
Me.SearchEmailSubform.Form.RecordSource = "Select * From SearchEmail " & BuildFilter
'Requery the subform
Me.Form!SearchEmailSubform.Form.Requery
End Sub
This is where I believe my problem is coming from, as the other listboxes refer to different tables.
Code:
strSQL = "SELECT * FROM [Account List] " & "WHERE " & "(" & strCriteria & ") AND (" & strCriteria1 & ") AND (" & strCriteria2 & ") AND (" & strCriteria3 & ") AND (" & strCriteria4 & ")" & "; "
I can't figure out the best way or any way to have it select from different tables.
Any suggestions would be helpful along with any improvements.
Thanks.