Building search/filter criteria

I am designing a form where I can search the disbursement info by
selecting 1 or more payment stages and 1 or more fund source. From
what the user select, build the where clause and pass it as a record
source to a subform. I have 2 listboxes, 1 button for Search and 1 sub
form for display result. My problem is how can I combine the two
listboxes in one where clause

'Listbox 1
Private Sub ListboxPaymentStage_Click()
Dim varItem As Variant
Dim Stg As String

Stg = "IN ('"
For Each varItem In Me.Combo0.ItemsSelected
Stg = Stg & Me.ListboxPaymentStage.ItemData(varItem) & "', '"
Next varItem

ZZ_Stages = Mid(Stg, 1, Len(Stg) - 4) & "')"

End Sub

'Listbox 2

Private Sub lstFundSource_Click()
Dim varItem As Variant
Dim Stg As String

Stg = "IN ('"

For Each varItem In Me.lstFundSource.ItemsSelected
Stg = Stg & Me.lstFundSource.ItemData(varItem) & "', '"
Next varItem

ZZ_Fund_SOurce = Mid(Stg, 1, Len(Stg) - 4) & "')"
End Sub


On my search button I had the code below

Private Sub Command8_Click()
Dim frmSQL As String
Dim St As String
Dim qrySTG As String

If Not IsNull(ZZ_Stages) Then
St = " WHERE (((frmCofundTable.Stages) " & ZZ_Stages & "));"
End If

If IsNull(ZZ_Fund_SOurce) Then
qrySTG = Null
Else
qrySTG = " WHERE )"
End If

frmSQL = "SELECT frmCofundTable.* FROM frmCofundTable " & St ''&
qrySTG
Forms![Main]![SubForm].Form.RecordSource = frmSQL
Me.SubForm.Requery

End Sub

Samnang Sao wrote:
I am designing a form where I can search the disbursement info by
selecting 1 or more payment stages and 1 or more fund source. From
what the user select, build the where clause and pass it as a record
source to a subform. I have 2 listboxes, 1 button for Search and 1 sub
form for display result. My problem is how can I combine the two
listboxes in one where clause

ONE way...
1. create a function that receives a reference to a multi-select listbox
2. make the function return a valid filter as a string, e.g.

[somefield] IN ('value1', 'value2', 'value3')

3. create a temporary string variable to collect the function's return value
strFilter = CreateFilter(me.lbxFirst)
strFilter = strFilter & " AND " & CreateFilter(me.lbxSecond)

4. append the filter to the SELECT statement...
strSQL = "SELECT {fieldlist} FROM {tablelist}... WHERE " & strFilter

5. assign to your form's recordsource
Me.RecordSource = strSQL
Me.requery 'don't think you even need this... but anyway.

Hope I havent completely confused you...
The *basic* idea here is to create a single function that can process the
selected items in each listbox and then return a *piece* of the final filter.
Then you can just AND/OR those together in your final SQL statement. Finally,
you just assign that complete query string to be the form's recordsource.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1