Filter String with Access VBA

[danneedham]

Hi Everyone,

I am trying to create a filter form to help users select the data that they would need.

At the moment it consists of 3 list boxes, where multiple items can be chosen.

When multiple items are chosen in some instances it doesnt return the correct data.

Please help!

<CODE>
Sub Command11_Click()

Dim AreaFilter As String
Dim StoreTypeFilter As String
Dim StatusTypeFilter As String

Dim Criteria As String ' Query String

Dim a As Variant ' Area variant
Dim b As Variant ' Store Type variant
Dim c As Variant ' Status Type variant

' AREA FILTER
' Build criteria string from selected items in list box.
AreaFilter = ""
For Each a In Me![AreaList].ItemsSelected
If AreaFilter <> "" Then
AreaFilter = AreaFilter & " OR "
End If
AreaFilter = AreaFilter & "[Area]='" _
& Me![AreaList].ItemData(a) & "'"
Next a

' STORE TYPE FILTER
' Build criteria string from selected items in list box.
StoreTypeFiler = ""
For Each b In Me![StoreTypeList].ItemsSelected
If StoreTypeFilter <> "" Then
StoreTypeFilter = StoreTypeFilter & " OR "
End If
StoreTypeFilter = StoreTypeFilter & "[StoreType]='" _
& Me![StoreTypeList].ItemData(b) & "'"
Next b

' STATUS TYPE FILTER
' Build criteria string from selected items in list box.
StatusTypeFiler = ""
For Each c In Me![StatusTypeList].ItemsSelected
If StatusTypeFilter <> "" Then
StatusTypeFilter = StatusTypeFilter & " OR "
End If
StatusTypeFilter = StatusTypeFilter & "[Status]='" _
& Me![StatusTypeList].ItemData(c) & "'"
Next c

' Add the query strings together to form the criteria.
' Check each query variant, if is null, replace with wildcard (to show all records)
If AreaFilter = "" Then
AreaFilter = "[Area] LIKE '*'"
End If
If StoreTypeFilter = "" Then
StoreTypeFilter = "[StoreType] LIKE '*'"
End If
If StatusTypeFilter = "" Then
StatusTypeFilter = "[Status] LIKE '*'"
End If

'Add all the different criteriors together to make the query string.
Criteria = AreaFilter & " AND " & StoreTypeFilter & " AND " & StatusTypeFilter

' Filter the form using selected items in the list box.
Form_frmStoreList.Filter = Criteria
Form_frmStoreList.FilterOn = True
MsgBox (Criteria)



End Sub
</CODE>


Thanks Dan

 

[danneedham]

Hi All

I think I have found an answer to my question.

When I combine all of the filter criteria. It just required brackets:

Criteria = "(" & Combo1 & ") AND (" & Combo2 &")"
Etc.

I think the problem was when there was more than one choice my code automatically generated Combo1 OR Combo1.

Hope this makes sense!

Dan