Query by Multiple Selection List Box (1 Viewer)

so i have two listboxes that have the values i want for the query parameters. I slightly modified one code i found so that i can query using one of them, however i cannot figure out how to get the second listbox to put criteria into another field. The working code i'm using is:

Private Sub Command_Click()
On Error GoTo Err_Command_Click

On Error GoTo Err_Handler
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String


For Each varItem In Me.PartyBox.ItemsSelected
strCriteria = strCriteria & "counterparties.counterparty =" & Chr(34) & Me.PartyBox.ItemData(varItem) & Chr(34) & " Or "
Next varItem


strCriteria = Left(strCriteria, Len(strCriteria) - 4)

strSQL = "SELECT counterparties.[Counterparty Entity], Fund.[Fund Name], products.Product, combine.[Available?] " & _
"FROM products INNER JOIN (Fund INNER JOIN (counterparties INNER JOIN combine ON counterparties.[Counterparty ID] = combine.[company id]) ON Fund.[Fund ID] = combine.[fund id]) ON products.[Product ID] = combine.[product id] " & _
"WHERE " & strCriteria


CurrentDb.QueryDefs("1").SQL = strSQL


DoCmd.OpenQuery "1"

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number = 5 Then
MsgBox "Must Make A Selection First", , "Make A Selection First"
Exit Sub
Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_Handler
End If


Dim stDocName As String
stDocName = "combqry"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command_Click:
Exit Sub

Err_Command_Click:
MsgBox Err.Description
Resume Exit_Command_Click

End Sub



I am trying to be able to search by Product and counterparty.
Any help is greatly appreciated, thanks!

ive tried adding a strcriteria2 and matching it to the products listbox, but i cant seem to get it into the WHERE section.

"WHERE " & strCriteria AND "& strcriteria2"

^the above does not work, i get "13 type mismatch" I cant simply put & strciteria either because it gets an error instanly in the vb editor. Any ideas how to get this to work?

zyxwvu44 said:

ive tried adding a strcriteria2 and matching it to the products listbox, but i cant seem to get it into the WHERE section.

"WHERE " & strCriteria AND "& strcriteria2"

^the above does not work, i get "13 type mismatch" I cant simply put & strciteria either because it gets an error instanly in the vb editor. Any ideas how to get this to work?

Click to expand...

Well, for one - if you truly have that syntax shown, then you have it slightoy off. It should be:

"WHERE '" & strCriteria & "' AND '" & strcriteria2 & "'"

boblarson said:

Well, for one - if you truly have that syntax shown, then you have it slightoy off. It should be:

"WHERE '" & strCriteria & "' AND '" & strcriteria2 & "'"

Click to expand...

When i put this in the query runs but its changing the field im searching in to some combination of the field and my search criteria eg:

'products.[product id] ="4"'

and the criteria turns to: <>False

i tried playing with the quotation marks and couldnt get it to work

Well, because your criteria already has the quotes built in, you don't want the extra single quotes, so:

"WHERE " & strCriteria & " AND " & strcriteria2

but you also need to watch out for your and's & or's. In other words, that's going to produce:

WHERE 1 OR 2 OR 3 AND a OR b OR c

That may or may not produce the expected results. Only you know what you actually want, but it might be:

WHERE (1 OR 2 OR 3) AND (a OR b OR c)

In any case, you probably need to add the appropriate parentheses to get the result you want.

thanks for the help pbaldy. this did get the query to work properly but i cant figure out the parenthesis. Should i be adding them into the "WHERE" section or in the part where I'm defining strcriteria and strcriteria2? your second example is how i want it to work. [(a or b or c) and (d or e or f)]

You're going to kick yourself, it's so easy:

"WHERE (" & strCriteria & ") AND (" & strcriteria2 & ")"

oh man, youre right. Thanks so much!

I ended up changing it around a bit to accomodate checkboxes, a third criteria, and any combination of the three. For people searching, here is the code:

Private Sub button_Click()

On Error GoTo Err_MakeFilterCriteria

Dim strCritString As String

Dim strBuildString As String

Dim strFullString As String

Dim intPosWhere As Integer

Dim intPosSemi As Integer

Dim qd As QueryDef

Dim rst As DAO.Recordset

Dim frm As Form

Dim booFirstFlag As Boolean

Dim intSelItem As Variant

booFirstFlag = False ' this flag shows whether a WHERE has yet been added

Set frm = Forms("search")

Set qd = CurrentDb.QueryDefs("1")

strFullString = qd.SQL ' gets the SQL from the existing query

' Trim any exisitng WHERE clause from the SQL

intPosWhere = InStr(1, strFullString, "WHERE")

intPosSemi = InStrRev(strFullString, ";")

If intPosWhere > 0 Then

strFullString = Left(strFullString, intPosWhere - 3)

Else: strFullString = Left(strFullString, intPosSemi - 1)

End If

' filter COUNTERPARTY

If frm.partycheck And frm.PartyBox.ItemsSelected.Count Then

booFirstFlag = True

strCritString = "WHERE ("

strBuildString = ""

For Each intSelItem In Me.PartyBox.ItemsSelected
strBuildString = strBuildString & "(counterparties.counterparty) =" & Chr(34) & Me.PartyBox.ItemData(intSelItem) & Chr(34) & " Or "
Next intSelItem

strBuildString = Left(strBuildString, Len(strBuildString) - 4)


strCritString = strCritString & strBuildString & ")"

End If

' filter PRODUCT

If frm.productcheck And frm.Products.ItemsSelected.Count Then

If booFirstFlag Then

strCritString = strCritString & " AND ("

Else

strCritString = "WHERE ("

booFirstFlag = True

End If

strBuildString = ""

For Each intSelItem In Me.Products.ItemsSelected
strBuildString = strBuildString & "(products.[product]) =" & Chr(34) & Me.Products.ItemData(intSelItem) & Chr(34) & " Or "
Next intSelItem

strBuildString = Left(strBuildString, Len(strBuildString) - 4)

strCritString = strCritString & strBuildString & ")"

End If


' filter FUND

If frm.fundcheck And frm.Funds.ItemsSelected.Count Then

If booFirstFlag Then

strCritString = strCritString & " AND ("

Else

strCritString = "WHERE ("
booFirstFlag = True

End If

strBuildString = ""

For Each intSelItem In Me.Funds.ItemsSelected
strBuildString = strBuildString & "(fund.[fund name]) =" & Chr(34) & Me.Funds.ItemData(intSelItem) & Chr(34) & " Or "
Next intSelItem

strBuildString = Left(strBuildString, Len(strBuildString) - 4)

strCritString = strCritString & strBuildString & ")"
End If


strFullString = strFullString & vbCrLf & strCritString

qd.SQL = strFullString

' Check for no hits

Set rst = CurrentDb.OpenRecordset("1")

If rst.BOF And rst.EOF Then

MsgBox "No Available Products"

Exit Sub

End If

rst.Close ' free up resources

' Open the selection form

DoCmd.OpenForm ("results")

Forms("Results")Refresh 'make sure new query is referenced

Exit_MakeFilterCriteria:

Exit Sub

Err_MakeFilterCriteria:

MsgBox Err.Description

Resume Exit_MakeFilterCriteria

End Sub